MySql LOAD DATA with subquery performance issue I'm migrating data into a MySql (5.7.26) database (32GB ram), running as a managed service on AWS. While importing the data, I need to map one of the columns of the CSV being imported to another value using a MEMORY table lookup; so my LOAD DATA resembles the following:

LOAD DATA LOCAL INFILE 'file.csv'
INTO TABLE table_1(col_1, @var1)
SET col_2 = (select mapped_value from table_2 where id = @var1)


table_2 is a 2-column (id, mapped_value) MEMORY table with 3.4MM rows.

When I import the CSV without the subquery, I get several million inserts per minute. However, when I run the same import with the subquery the LOAD DATA performance degrades to near zero (~100 inserts per minute). Is this to be expected with a subquery, or is there something I'm doing wrong in the example above?
This is probably due to the fact that memory tables by default use a hash index instead of a btree index. Read more about it here.
Or maybe you don't use an index at all in your memory table?
A pretty similar performance problem is described in this blog post: Performance gotcha of MySQL memory tables
If you don't have an index on the id column, add one (btree!)

The MEMORY storage engine supports both HASH and BTREE indexes. You can specify one or the other for a given index by adding a USING clause as shown here:
   CREATE TABLE lookup
       (id INT, INDEX USING HASH (id))
       ENGINE = MEMORY;
   CREATE TABLE lookup
       (id INT, INDEX USING BTREE (id))
       ENGINE = MEMORY;


If you already have an index on that column, check with
show indexes from table_2;

of what index_type your index is. Convert to btree if necessary.