1

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.

3
  • Save your data for 400k row which must be inserted into your table to the temporary table then insert from this table without correlated subqueries.
    – Akina
    Sep 10, 2023 at 19:11
  • @Akina saving into a Temp table does no take as much time as in a normal table ?
    – moe_
    Sep 10, 2023 at 19:22
  • Your temptable may use ENGINE=Memory. Your goal is not to iterate and insert the rowss one-by-one but perform batch insert, insert all rows with one SQL query.
    – Akina
    Sep 10, 2023 at 19:49

2 Answers 2

2

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.

0
  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.)

4
  • Hi @Rick James, I am trying to implement your suggestion, LOAD DATA LOCAL INFILE 'Final_df.csv' INTO TABLE answer_temp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; but I'm facing this error (MySQLdb.OperationalError) (2068, 'LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.') ---> even though local_infile is set to ON ---> I also tried this fix stackoverflow.com/a/63388772/12944030, but still not working, any suggestion would be very much helpful, thanks
    – moe_
    Oct 11, 2023 at 9:25
  • 1
    @moe_ - The "mysql" user (in the OS) needs read permission to the file and read+search permission to the directories above it. Are you using Windows or some variant of Linux? For the latter, see chmod and related commands.
    – Rick James
    Oct 11, 2023 at 16:35
  • I am using Ubuntu, and the mysql user is "root" so it suppose to have permission
    – moe_
    Oct 12, 2023 at 11:29
  • @moe_ - The MySQL server ("mysqld") is running as the OS user "mysql". See top or ps.
    – Rick James
    Oct 12, 2023 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.