Consider a PHP-MySQL application where the information of 1000 users is being retrieved from the database by running a for loop:
for($i = 1; $i <= 1000; $i++){
$query = "SELECT * FROM user WHERE user_id = $i";
//run the query and fetch data
}
In each iteration, the first thing the MySQL engine does is to parse the query for syntax check. Then it sets up the query and runs it. Since the query remains unchanged during each iteration(except for the value of user_id), parsing the the query each time is definitely an overhead. In such cases use of prepared statements is most convenient. A prepared statement is just like a typical query, except that it has ‘placeholders’ that are supplied values at run time. The prepared statement in this case will look like this:
"SELECT * FROM user WHERE user_id = ?"
Notice the placeholder(‘?’) for the value of user_id in the query. Now MySQL engine needs to parse the query only once, then execute it 1000 times by binding the placeholder with PHP script supplied value for user_id. This pre-parsing of the query results in a significant performance boost.
The MySQL Improved extension in PHP, more commonly known as MySQLi, provides an API to work with prepared statements. The documentation at the online PHP manual is good enough to get you started on how to use them on your PHP application, so I’ll not go through it. Instead, I am going to share the results of my personal experiments on comparing performances of traditional and prepared SQL statements.
I conducted the experiment on a demo project which has large amount of data. I wrote two separate scripts on our development server, both of which performed the same operation: joining two related tables (one of which has over 150,000 records, the other has 350,000) and fetching some data . One script used regular SQL statement, the other employed prepared statement techniques. Each script was executed three times and the time required to fetch the data was measured at each pass.
The First script: traditional SQL statement
//Get the Database link
$dbLink = getDBLink();
$timeStart = microtime(true);
for($i = 0; $i < 162038; $i++){
$query = "SELECT article_id, article_name, username as author FROM articles a LEFT JOIN user u ON (a.author_id = u.user_id) WHERE article_id = $i";
if($result = $dbLink->query($query))
$obj = $result->fetch_object();
else die("Failed to execute query: $dbLink->error");
$result->close();
}
$timeEnd = microTime(true);
$dbLink->close();
//measure the time difference
$timeDiff = $timeEnd - $timeStart;
echo "Total time: $timeDiff seconds";
Output:
First Pass -> Total time: 25.5793459415 seconds
Second Pass -> Total time: 25.1708009243 seconds
Third Pass -> Total time: 25.2259421349 seconds
Average: 25.32536300023 seconds
The Second Script : using prepared statement
$dbLink = getDBLink();
$query = "SELECT article_id, article_name, username as author FROM article a LEFT JOIN user u ON (a.author_id = u.user_id) WHERE article_id = ?";
$stmt = $dbLink->stmt_init();
if(!$stmt->prepare($query))
die("Failed to prepare statement: ".$dbLink->error);
$timeStart = microtime(true);
for($i = 0; $i < 162038; $i++) {
//bind the parameter
$stmt->bind_param('i',$i);
//execute the statement
$stmt->execute();
//bind the result, fetch it, then free it
$stmt->bind_result($articleId, $articleName, $author);
$stmt->fetch();
$stmt->free_result();
}
$timeEnd = microTime(true);
$stmt->close();
$dbLink->close();
//measure the time difference
$timeDiff = $timeEnd - $timeStart;
echo "Total time: $timeDiff seconds";
Output:
First Pass -> Total time: 20.1434290409 seconds
Second Pass -> Total time: 20.182309866 seconds
Third Pass -> Total time: 20.6448199749 seconds
Average: 20.32351962726 seconds
The task takes 20% less time for prepared statement, a significant performance boost.
Other than performance, it can also improve application security by guarding against SQL Injections. Check out this informative blog post on that topic.
Harrison Fisk at MySQL AB wrote a very good article on MySQL prepared statements. Don’t forget to check out the section ‘When should you use prepared statement?’ if you read it.
