0

I've got 25,000,000 codes (each code has 8 random literals) to insert into my database. We're running with MySQL and Apache Torque as ORM.

My table simplified looks like this: 

table_code
id BIGINT, Primary Key (no auto-increment, bc of ORM)
code VARCHAR

What is the most efficient way to insert them? I tried this with generating Torque-Objects over night, but since the ID needs to be autoincremental, it takes forever.

Any ideas are much appreciated :) Thank you!

Remo
  • 1,112
  • 2
  • 12
  • 25
  • 3
    The most efficient way is not to use the ORM, but raw SQL queries. This applies to any application using an ORM. – Mike Doe Jan 25 '19 at 10:07
  • Where does the codes come from? What do you need 25 Mil random strings for? – Patrick Jan 25 '19 at 12:04
  • Is there an index on `code`? It makes a _huge_ difference. Why do you need `id` at all? Please provide `SHOW CREATE TABLE` and the queries that need to reach into the table. – Rick James Jan 26 '19 at 17:24
  • Just updated my question. I don't have any index on code, just use ID as a PrimaryKey. – Remo Jan 27 '19 at 18:31

1 Answers1

2

Hello I think the best is to use LOAD DATA INFILE:

A link explain it's faster here : https://medium.com/@benmorel/high-speed-inserts-with-mysql-9d3dcd76f723

For your table juste do something like this : 

LOAD DATA INFILE '/path/to/mycodes.csv' INTO TABLE table_code;

For more information about this function : https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Daniel E.
  • 2,440
  • 1
  • 14
  • 24
  • +1 This is extremely fast. I'm not sure about generating the autoincrement IDs on the fly, though - might be faster to load all data first, and then create IDs afterwards. – D'Arcy Rittich Jan 25 '19 at 13:09
  • it will be a bit slower with the auto increment, the best is to test it. – Daniel E. Jan 25 '19 at 13:20
  • What version of MySQL? Newer versions have faster index creation. Anyway, it is virtually no effort to create the `AUTO_INCREMENT` as they are inserted _in this case_. And it would be a big effort to create the id afterward. – Rick James Jan 26 '19 at 17:27
  • Thank you @DanielE. Didn't know about that one. I need to check the security settings with the database administrator. Problem still going to be the auto-increment-stuff. Is altering the Primary Key Attribute to auto-increment for this import (and remove it after that) a good idea? – Remo Jan 27 '19 at 18:34
  • As said before, it won't be so expensive to keep the auto increment – Daniel E. Jan 27 '19 at 18:41