PHP MySQL prepared SQL statement vs SQL statement

Lot of manuals telling that if you do care about high performance using MySQL you should learn about prepared statements. In some cases I am using them for some time, but always wanted to ensure myself thet prepared statements are providing some speed emprovement. Preparing this article I have created some test script and run a few tests to compare ordinary SQL statements and prepared ones.

By the way, if you are using MySQL, than you might be interested to read about MySQL master-slave and master-master replication and also about MySQL partitioning and subpartitioning.

MySQL native SQL syntax for prepared queries

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

One more example

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;

Important to remember that prepared statement should be dealocated, because MySQL can handle only limited number of prepared statements and if resources will not be deallocated properly than this may cause problems to your application and the system in general.

MySQL prepared statements in PHP

PHP code that shows prepared statements usage:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$city = "Amersfoort";

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($district);

    /* fetch value */
    $stmt->fetch();

    printf("%s is in district %s\n", $city, $district);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();
?>

I should mention here that it is very important to close prepared statements to deallocate resources correctly.

MySQL query vs prepared query speed test

Okay, now when basics of prepared statements are explined briefly I can go on with speed tests. Here is a test table:

CREATE TABLE `test_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Code` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Code` (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And here is the code of my test script. It is very simple and tests just a couple of patterns. Basically it inserts random string info a table that contains 2 keys - autoincremental primary key and key by the text field. This is important for the InnoDB table mainly, as keys rebuilt takes quite a lot of time and slows down rows insertion process. I am not compring InnoDB against MyISAM tables, but just want to understand how prepared statement perform on both table types. So, the code of the test script:

<?php
$mysqli = new mysqli("localhost", "root", "password", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$cnt = 10000;

// SQL query
dropAll();
$start = microtime(true);
for($i = 0; $i<$cnt; $i++){
	$code = md5(mt_rand());
	$mysqli->query("INSERT INTO test_table SET Code='".$mysqli->escape_string($code)."'");
}
$end = microtime(true);
echo "Elapsed time. SQL query: ".($end-$start)." sec<br/>";

// SQL prepared each
dropAll();
$start = microtime(true);
for($i = 0; $i<$cnt; $i++){
   $stmt = $mysqli->prepare("INSERT INTO test_table SET Code=?");
   $code = md5(mt_rand());
   $stmt->bind_param("s", $code);
   $stmt->execute();
   $stmt->close();
}
$end = microtime(true);
echo "Elapsed time. SQL prepared each: ".($end-$start)." sec<br/>";

// SQL prepared once
dropAll();
$start = microtime(true);
$stmt = $mysqli->prepare("INSERT INTO test_table SET Code=?");
for($i = 0; $i<$cnt; $i++){
   $code = md5(mt_rand());
   $stmt->bind_param("s", $code);
   $stmt->execute();
}
$stmt->close();
$end = microtime(true);
echo "Elapsed time. SQL prepared once: ".($end-$start)." sec<br/>";
dropAll();

/* close connection */
$mysqli->close();

function dropAll(){
	global $mysqli;
	$sql_del = "DELETE FROM test_table";
	$mysqli->query($sql_del);	
	
	echo "Number of rows removed: ".$mysqli->affected_rows." <br/>";
}


?>

MyISAM test results (10000 records):

Elapsed time. SQL query: 2.1698110103607 sec
Elapsed time. SQL prepared each: 3.8337211608887 sec
Elapsed time. SQL prepared once: 2.2286930084229 sec

InnoDB test results (100 records):

Elapsed time. SQL query: 2.5044569969177 sec
Elapsed time. SQL prepared each: 2.6132278442383 sec
Elapsed time. SQL prepared once: 2.4961848258972 sec

Than I thought that prepared statements should be much faster for the remote database server, because prepared statements should not transfer all the request, just binary data. So I took two servers on different continents and tested thescript once again connecting from one server to another with MyISAM table. Speeds are lower, but results are pretty much the same - speed is almost equal.

Elapsed time. SQL query: 13.446207046509 sec
Elapsed time. SQL prepared each: 26.709115982056 sec
Elapsed time. SQL prepared once: 13.645385026932 sec

As a conclusion I should notice that there is no much sense to use prepared queries if you have to execure just one or two queries, for example in a PHP script that generates the page. This approach on a heavy loaded server may take twice more resources than script with an ordinary SQL queries. And looks like it have sense to use prepared statements only when you have to execute the script lot of times and mainly because you preffer this approcah and if it simplifies the script itself, as there is no major speed improvement. Friendly speaking this result is quite unexpected for me, as I thought that prepared statements work much faster than ordinary queries at least bypassing SQL parsing.

By the end of the test the question stays open for me - what is the reason that should make me use prepared statements, if they do not provide speed improvement and make things more complicated, at least by the fact that you should bother to deallocate resources? I have not found the answer for myself.... And you?

UPDATE:

I got so much negative comments regarding this article that was surprised even more than by the test results :) So, I decided to explain some aspects of the test I have run.

  • Script tests just prepared vs not prepared statements, not MySQL itself or storage engines.
  • I am just trying to understand which one is better suted for certain cases.
  • SQL injections are not the reason to write slower code, php mysql driver provides several ways to escape the input.
Posted by:
Enjoyed this post? Share and Leave a comment below, thanks! :)