Need to optimize the insert query in php mysql

Question

I am using following code to insert multiple records into a table. As you can see the query is in a loop. Every time the loop executes, seperate sql query is run to insert it.

I want a approach where instead of running multiple queries, only single INSERT sql query should run with multiple insert statements.

Though this code of mine is functioning perfectly but I need to optimize it.

$managed_ailments = unserialize($ailments);
foreach($managed_ailments as $ailment_id)
{
    $sql = "INSERT INTO ". TABLE_PATIENTS_AILMENTS_RECORDS ."";
    $sql .= "(patient_id, ailment_id, datecreated) VALUES";
    $sql .= "($patient_id, $ailment_id, now())";
    $query = $mysql->query($sql);
}

Here $managed_ailments is a serialized array which i have unserialized. There can be one or more than one values in the array. $mysql->query($sql) is my custom function.

Please help me to convert this code into a single sql query which does it all.

Thanks in advance.

What is the purpose of ."" at the end of the 4th line? You're appending an empty string. — awm
– awm, Commented Aug 15, 2012 at 17:37
You don't seem to be doing any SQL escaping here. This is terrifying. — tadman
– tadman, Commented Aug 15, 2012 at 19:21
Using batched inserts as a couple of the respondents suggest is a good idea as a batchsize of 10, say, reduces the table locks and SQL RPCs by ten-fold. Going to 100x isn't really worth it. Ditto Motjaba's suggestion of using prepared statements. If you are doing, say, 50+ inserts at a go then it might be worth doing and outer loop 0,10,20,... processing batches of 10 using a prepared statement and a second close-outloop doing the last 1-9 if needed. — TerryE
– TerryE, Commented Aug 16, 2012 at 17:28

Telgin · Accepted Answer · 2012-08-15 19:49:08Z

3

Something like this should work:

$managed_ailments = unserialize($ailments);
$sql = "INSERT INTO ". TABLE_PATIENTS_AILMENTS_RECORDS ."";
$sql .= "(patient_id, ailment_id, datecreated) VALUES (";

$query_parts = array();

foreach($managed_ailments as $ailment_id) {
    $query_parts[] = "($patient_id, $ailment_id, now()";
}
$sql .= implode(",", $query_parts);
$sql .= ")";

$query = $mysql->query($sql);

edited Aug 15, 2012 at 19:49

answered Aug 15, 2012 at 17:36

Telgin

1,60410 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TerryE Over a year ago

what happens if $ailments is large enough that your $sql is larger than the maximum allowed packet on the target system?

Telgin Over a year ago

@TerryE I'm not sure, I'm not aware of any such max packet size in MySQL, unless you mean the network packets. In the latter case it shouldn't matter. If there is a max query length for MySQL, you'd just have to find some way to break the query up and only insert X records at once.

TerryE Over a year ago

See Packet too large. This is typically 16Mb. But its a constraint and some time you might hit it -- and then your code will fail silently. There is another issue here -- that if you are updating 10K rows, say, then the table (if MyISAM) will be locked for quite a long time, and any other requests wanting the table will stall.

Asnexplore Over a year ago

@TerryE - In my case the table is very small, say less than 200 records, so it wont be of any worry to me

Rostyslav Dzinko · Accepted Answer · 2012-08-15 18:25:53Z

2

you can add a value statement for each recordthen exectute the query, i.e:

INSERT INTO example 
    (example_id, name, value, other_value)
VALUES
    (100, 'Name 1', 'Value 1', 'Other 1'),
    (101, 'Name 2', 'Value 2', 'Other 2'),
    (102, 'Name 3', 'Value 3', 'Other 3'),
    (103, 'Name 4', 'Value 4', 'Other 4');

edited Aug 15, 2012 at 18:25

Rostyslav Dzinko

41k5 gold badges55 silver badges66 bronze badges

answered Aug 15, 2012 at 17:37

bmgarstka

212 bronze badges

Comments

Mojtaba · Accepted Answer · 2012-08-15 17:43:07Z

You can use MySQLi extension which has prepared statement. You will send the template once to the server then withen a loop you just send data.

//initialize and connect $mysqli via MySQLi extension 


$sql = "INSERT INTO ". TABLE_PATIENTS_AILMENTS_RECORDS ."";
$sql .= "(patient_id, ailment_id, datecreated) VALUES";
$sql .= "(?, ?, now())";

$stmt = $mysqli->prepare($query)

$managed_ailments = unserialize($ailments);
foreach($managed_ailments as $ailment_id) {
    $stmt->bind_param("ii", $patient_id, $ailment_id);
    $stmt->execute();
}

Note that I'm assuming both $patient_id, $ailment_id are integers. And also assuming you don't want to keep the results of executed queries, because you are overwriting the $query

This will reduce the overhead of NOW() function and save some bandwidth.

Here is a link to PHP MySQLi Documentation

Collectives™ on Stack Overflow

Need to optimize the insert query in php mysql

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related