bbruman bbruman - 25 days ago 8
JSON Question

MySQL multi_query Very Slow, Alternative for Simple Truncate Query?

I have a PHP script set-up that parses over a JSON file that is split up in multiple pages.

This PHP script parses the JSON and inserts it into a MySQL database.

On a single query... (without the

TRUNCATE
statement):

if ($count > 0) {

//check toperform operation

foreach ($jsondecode as $entries) {

//getting variables here

$sql = "INSERT INTO table (title, handle, imagesrc)
VALUES ('".$title."', '".$handle."', '".$imagesrc."')";

if ($connect->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $connect->error;
}
}
}


Results successfully with a
script execution time: 16.451724052429


On a multi_query....:

if ($count > 0) {
$sql = "TRUNCATE table;";

foreach ($jsondecode as $entries) {

//getting variables here

$sql.= "INSERT INTO table (title, handle, imagesrc)
VALUES ('".$title."', '".$handle."', '".$imagesrc."')";

if (!$mysqli->multi_query($sql)) {
echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
if ($res = $mysqli->store_result()) {
var_dump($res->fetch_all(MYSQLI_ASSOC));
$res->free();
}
} while ($mysqli->more_results() && $mysqli->next_result());


}
}


Results successfully with
script execution time: 278.05182099342
, almost 5 minutes.

All I am trying to do is
TRUNCATE
the table before the
INSERT
.

I am going to be running this on a web server CRON job which is going to execute this script every 12 hours.

There is obviously such a huge execution time difference in the single query vs multi-query... is there anything I can do?

My only thought is to setup another CRON job script that simply does the
TRUNCATE
statement every 12 hours, but 1 minute before this main one runs. This seems like it should work.. but is of course not ideal as then I'd have to deal with multiple scripts instead of just one.

Answer

The reason for such a huge difference the presence of one extra character!

$sql.= "INSERT INTO table (title, handle, imagesrc)
VALUES ('".$title."', '".$handle."', '".$imagesrc."')";

You keep on adding a new query to the existing query but you continue to execute that ballooning query inside the loop. It may not have been obvious because you have not properly indented your code. So the crucial mistake here is indenting!!

You are truncating the table for each line of input in your JSON, and then inserting the whole thing all over again.

besides, this really isn't a case where you ought to be using multi query. Run the truncate query outside the loop. Then run the insert query inside the loop.

As others have pointed out building a single insert query with multiple VALUES sets might be a bit faster. Alternatively turn transaction auto commit to off and switch back on at the end.

if ($count > 0) {
    $connect->query('TRUNCATE `table`');

    //check toperform operation

    foreach ($jsondecode as $entries) {

        //getting variables here 

        $sql = "INSERT INTO `table` (title, handle, imagesrc)
        VALUES ('".$title."', '".$handle."', '".$imagesrc."')";

        if ($connect->query($sql) === TRUE) {
            echo "New record created successfully";
        } else {
            echo "Error: " . $sql . "<br>" . $connect->error;
        }

    }
}