Insert a large amount of data into the database in very small inserts

Question

So, I have a database that has a lot of data inserted from a Java application. Usually I insert into table1, get the last identifier, then insert it into table2 again and get the last identifier from there, and finally insert into table3 and get this identifier, and also work with it in the application. And I insert about 1000-2000 rows of data every 10-15 minutes.

And using a lot of small inserts and selections on a production web server is not very good, because sometimes it fights with the server.

My question is: is there a way to insert multiple data into table1, table2, table3 without using so many samples and inserts? Is there an sql-fu technology that I'm skipping?

Answer 1

Since you probably rely on the primary keys of auto_increment, you need to do the inserts one at a time, at least for table1 and table2. Because MySQL will not give you more than the most recently generated key.

You will never have to choose. You can get the last inserted identifier from Statement using the getGeneratedKeys() method. See an example showing this in the MySQL manual for Connector / J:

http://dev.mysql.com/doc/refman/5.1/en/connector-j-usagenotes-basic.html#connector-j-examples-autoincrement-getgeneratedkeys

Other recommendations:

• Use the multi-line INSERT syntax for table3.
• Use ALTER TABLE DISABLE KEYS during import and turn them back on when done.
• Use explicit transactions. That is, start a transaction in front of your data loading program and complete it at the end. I will probably also commit after every 1000 rows of table1.
• Use prepared statements.

Unfortunately, you cannot use the fastest method for bulk data loading, LOAD DATA INFILE , because this does not allow you to get the generated identifier values ​​in a string.

Answer 2

You can talk a lot here:

• The network delay is probably killing you if each of these INSERTs is a different network route. Try batch requests, so the whole transaction requires only one return route.
• Speaking of transactions, you do not mention them. If all three INSERTs need to be a single entity, you are better off handling transactions properly. If you do not know how best to research them.
• Try caching queries if they are reused. The fastest rounded route is the one you don't do.

Answer 3

You can reconfigure your database so that the primary key is not generated by the database, an automatically incremented value, but rather a generated UUID client. Then you can generate all the keys for each record in advance and batch inserts as you like.