If your MySQL version is 3.22.5 or later, you can now INSERT multiple values / records in one single query. In most cases this is probably not useful but I recently found this out while trying to optimise my GIDTopsites script.

I had a function in my script that empties a given MySQL table daily and automatically populates the empty table with a summary of ‘stats’ with data off another database table. Though not quite the same, my sample below will give you a vague idea how I eventually optimised my script using multiple inserts. First, a sample empty table to start with:

TABLE: beautiful
=================
id  | name   | age
----+--------+---
    |        |

As usual, the id field is an auto-increment field and our PHP script will just INSERT multiple values for the name and age columns (fields).

Multiple values in a single INSERT query example

In our sample PHP code below, we will INSERT 5 different records using just one MySQL INSERT query:

// Our MySQL query

$sql = "INSERT INTO beautiful (name, age)
 VALUES
 ('Helen', 24),
 ('Katrina', 21),
 ('Samia', 22),
 ('Hui Ling', 25),
 ('Yumie', 29)";

mysql_query( $sql, $conn );

That’s it! Assuming you had a MySQL table named `beautiful` all set up, you could save the sample script above and run it; and each run of the script will insert the five records at the same time every time!

Like I said, it’s wonderful to use something like this when you fill-up a certain MySQL table with (a summary of) data off other tables or an array.

The MySQL documentation randomly states this, under the Other Optimization Tips:

Use the multi-line INSERT statement to store many rows with one SQL command (many SQL servers supports this).