Add indexes to migrate_map_* tables

@hussainweb can you share total time of those big migrations with and w/o the patch? I'm seeing a significant improvement on my side, although no query lasted so long as you reported.

I'm testing the patch with two migrations:

* 150k core files.
* 28k nodes. Configured with several migration processes, so it is consulting maps intensively. Process migrations are configured for users (111 items in the map), files (148k items), terms (72 items) and nodes (33k items across 7 maps).

After patch I see an improvement of ~200s and ~1900s respectively.

Without patch:

* File: Processed 148278 items (148278 created, 0 updated, 0 failed, 0 ignored) - done with 'abo_media__files' [1260.55 sec, 339.58 MB]
* Node: Processed 29014 items (23467 created, 5503 updated, 44 failed, 0 ignored) - done with 'abo_articles__news' [12426.82 sec, 442.34 MB]

Patched:

* File: Processed 148278 items (148278 created, 0 updated, 0 failed, 0 ignored) - done with 'abo_media__files' [1046.27 sec, 339.58 MB]
* Node: Processed 29014 items (23467 created, 5503 updated, 44 failed, 0 ignored) - done with 'abo_articles__news' [10492.26 sec, 442.35 MB]

Also I'm seeing the gain in the node migration increases as the migration advances:

#100th node
Importing node 91643 [14.77 sec, 323.24 MB]
Importing node 91643 [14.18 sec, 323.24 MB]
gain: 0.59s

#500th node
Importing node 134921 [34.02 sec, 328.1 MB]  
Importing node 134921 [32.86 sec, 328.1 MB]
gain: 1.16s

#1000th node
Importing node 150625 [63.12 sec, 328.97 MB]
Importing node 150625 [59.38 sec, 328.97 MB]
gain: 3.74s

#3000th node
Importing node 179873 [280.15 sec, 340.78 MB]
Importing node 179873 [232.77 sec, 340.78 MB]
gain: 48s

#5000th node
Importing node 196131 [597.2 sec, 350.05 MB]
Importing node 196131 [497.89 sec, 349.81 MB] 
gain: 100s

#10000th node
Importing node 236129 [1802.89 sec, 378.34 MB]
Importing node 236129 [1533.05 sec, 378.34 MB]
gain: 269s

#15000th node
Importing node 257473 [3729.33 sec, 389.81 MB]
Importing node 257473 [3195.08 sec, 389.82 MB]
gain: 534s

#20000th node
Importing node 276783 [6316.9 sec, 421.27 MB]
Importing node 276783 [5456.51 sec, 421.27 MB]
gain: 860s

#25000th node
Importing node 296347 [9619.2 sec, 433.12 MB]
Importing node 296347 [8407.38 sec, 433.13 MB]
gain: 1212s

#28000th node
Importing node 307477 [11927.59 sec, 441.05 MB]
Importing node 307477 [10135.5 sec, 441.05 MB]
gain: 1792s

On patch review, I've identified what operation benefits each of the indexes.

1. +++ b/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php
   @@ -313,8 +313,10 @@ protected function ensureTables() {
   +        $indexes['source'][] = $mapkey;
   

   public function lookupDestinationIds(array $source_id_values) is the one that benefits this index. It is called by migration process plugin and MigrateExecutable::import().

2. +++ b/core/modules/migrate/src/Plugin/migrate/id_map/Sql.php
   @@ -333,6 +335,7 @@ protected function ensureTables() {
   +        $indexes['destination'][] = $mapkey;
   

   public function lookupSourceID(array $destination_id_values); is the one that benefits this patch. It is called by id_map\Sql::deleteDestination() and dedupe_entity source plugin.

The `source` index has a big impact on any migration, while the `destination` one has a limited scope -- at least in my setups where I've never used `dedupe_entity`.

Other interesting index may be on column `source_row_status`. It is used by SqlBase. I'll try and test it in a few hours.

+++ b/core/modules/migrate/tests/src/Unit/MigrateSqlIdMapEnsureTablesTest.php
@@ -72,6 +72,10 @@ public function testEnsureTablesNotExist() {
+      'indexes' => [
+        'source' => ['sourceid1'],
+        'destination' => ['destid1'],
+      ],

I'd like to see a test with multiple destination and multiple source ids that are indexed. Not just a single of each.

re #10: to be more clear, the reason I wanted to see multiple columns indexed for either source or destination is because I think they should have a combined index and I don't think they are setup to combine them. Because you'd always be looking up with both values, no?

re #8. Did some tests with an index for `source_row_status` column. I don't see a gain in migrations run (`drush migrate-import`). Only observed a little gain in `drush migrate-status` with cold mysql caches, but nothing relevant.

@hussainweb yeah, nixing the destination is fine for now. If we find a need for them, we can add them later. Since you found that it creates a combined index, we should add a test to verify that doesn't change.

Looks good now. Ready for a committer.

Very nice find. Committed/pushed to 8.3.x and 8.4.x, thanks! Tagging and bumping status retrospectively.

Hi!

Rollback of 500 000 records occupied 5 hours:
DELETE FROM migrate_map_upgrade_d7_url_alias WHERE destid1 = '189521'
When I add index:
CREATE INDEX destid ON migrate_map_upgrade_d7_url_alias (destid1) USING BTREE;
it occupied 10 minutes.

This was committed and closed some time ago. If there is still an issue, please open a new bug report.