How to do multiple MySQL INSERTs in a way that scales (PHP)

Question

We have a loop in our PHP code which inserts rows into a table. e.g.:

while ($info = mysql_fetch_assoc($someQuery)) {
    mysql_query("INSERT INTO X (i,j) VALUES ($info['ii'],$info['jj'])");
}

This was fine a few months ago because the loop would only iterate several times. However, due to our website getting more traffic this loop now sometimes iterates 1000 or more times. The table has some overhead (4,305 KiB) and SELECTs from this table are appearing in the MySQL slow-log, probably because they are having to wait for a long list of INSERTs to release the locks?

How should I optimise the code so it can scale better?

Some things I thought I might try:

• INSERT DELAYED - Need to look into it. Could it help?
• Try inserting multiple rows in the same query. But what limit should I set? 50, 500, 1000?

Answer 1

Instead of repeating mysql_query(), use prepared statements. They are a much more efficient way of repeating the same query many times with different values.

Also, I would look into those queries that are appearing in the slow log. Use EXPLAIN (<query>) to check that indexes are being used.

Answer 2

What is $someQuery? Could you use INSERT ... SELECT syntax?

Answer 3

What Ben James said is important. Prepared statements are much faster if you execute the same SQL, and just change parameters in it.

Also, You my try to change the whole loop to something like:

INSERT INTO x (i, j) SELECT (here goes your $someQuery)

Of course you have to adapt the $someQuery, so I selects only two columns which are of the same type (or can by cast automaticly) as the columns i and j.

If you do not have very complicated mechanism in php, then this shloud be possible and will be much faster than any php loop.

Answer 4

You can also build up a statement like this:

INSERT INTO X (i,j)
VALUES ($info['ii'],$info['jj']),
(val, val),
...
(val, val);

You can experiment with different limits to determine where, if anywhere, your SQL gets too long. Then you can set the limit to something sane.

Answer 5

One of the fastest ways to get data into MySQL is LOAD DATA INFILE. Think of it as a CSV import. You could write your data one row at a time to disk and then do a bulk load. According to this page on insert speeds LOAD DATA INFILE is 20x faster than a straight INSERT.

There may be other undesirable side-effects, though, as the table may well be locked for the whole process rather than just one row at a time. Running this in e.g. 100 row batches may make both parts of the problem manageable - I think you'll just need to experiment.

Answer 6

There are stored procedures and function in new versions of MySQL. You can use there a structured language with variables, loops and conditional blocks. One stored procedure should be faster than many independent inserts. However, it is a new language to learn.

You need mysqli extension in php, to use stored procedures.

You can also look at mysqli_multi_query.
http://pl.php.net/manual/en/mysqli.multi-query.php