optimize insert operation in MySQL (InnoDB)

Question

I have this table:

create table tab3(
id                              int not null auto_increment,
phrase                          text,
link_1                          int,
link_2                          int,
primary key (id),
foreign key (link_1) references tab1 (id),
foreign key (link_2) references tab2 (id));

I am inserting around 400k rows into this table with Python. this is the insert statement:

INSERT INTO tab3(phrase, link_1, link_2)
    VALUES(
        %s,
        (select id from tab1 where tab1.col1 = %s),
        (select id from tab2 where tab2.col2 = %s));

I have an index on both tables tab1.col1 and tab2.col2. but the insertion is taking a long time around 5mins/1000 row

I've tried many different techniques from the official docs of MySQL such as:

• using cursor.execute(stmt,param)
• using cursor.executemany(stmt, params)
• multiple processes ( billiard https://pypi.org/project/billiard/ )
• blocking the commit until all chunk of data is inserted and then commit changes
• encapsulating the insert stmt inside one transaction ( with START TRANSACTION )

But None of the above gave a good improvement.

Answer 1

Using MariaDB there is this inbuilt improvement in MariaDB-10.7+ for bulk inserting.

This is for empty tables when foreign_key_checks=0 and unique_checks=0.

In general, regardless of version, consider increasing innodb-buffer-pool-size to cover the tab3 created information and the tab1/tab2 data being read so most of it is in memory.

Earlier versions may benefit from an increased innodb-log-file-size.

Also look at your phrase. If this contains short amounts of text a varchar(max num of characters) is a better storage type as it avoids some innodb bulk size handling code that does some size/speed tradeoffs.

Answer 2

1. Create a temp table; have extra (NULLable) columns for the ids.
2. Insert the data into that table. Use LOAD DATA or batched Inserts (1000 rows) at a time.)
3. Do a single UPDATE to batch the setting of the ids for one column. Ditto for the other column(s).
4. INSERT...SELECT top copy all the rows into the 'real' table.
5. DROP the table from Step 1.

Here is a discussion of that job. It also includes inserting new rows, if necessary, into the normalization table(s) (your tab1 and tab2): Normalization

This will be a lot faster (about 1 SQL per 1000 rows) than what you have (3 SQLs per row).

(This technique applies whether the target table is empty or not.)