Last week I had a MySQL DBA/Developer tell me, “MySQLi sucks” when I asked why some code I was reviewing used the mysql_* extensions provided in PHP instead of mysqli_*. This really didn’t sit well with me. I turned to a trusted source for PHP information, and found they recommend mysqli_* for connecting to MySQL servers versions 4.1.3 and above. Although, I read many anecdotal stories about how mysqli_* was faster, I was unable to find any quantified proof of just how much faster. So in keeping with my performance measurement track I’ve been on this week, I decided to conduct my own test. I was surprised by the result. MySQL is faster in some cases.
The database I used is a pet project of mine that tracks some vehicle information. It uses a varchar primary key based on an automotive VIN number and the sample table I queried against has about 30K rows. The average row length is about 150bytes. I conducted 2 types of tests. The first was a completely unrealistic test measuring how fast a single row could be returned using each of the extension types. The other also returned a random sampling of rows. Since this isn’t a performance test of MySQL, I allowed each test to run one time completely to ensure the query cache was fully populated in MySQL. Then each test was run 3 subsequent times. Each test executed 1,000,000 queries to the server.
Single Hard Coded Query:
The first listing is the mysql_* calls. You’ll notice the
mysql_free_result()
function is commented out. I did test with this enabled and disabled and found that with this function enabled the queries returned at the same rate as with it enabled. So in the interest of fairness, I disabled it to more closely mimic the code executing with the mysqli_* calls.
<?php
$tq = 1000000;
$mt = microtime(true);
$conn = mysql_connect("127.0.0.1","user","");
mysql_select_db("test_schema", $conn);
for($i=0; $i<$tq; $i++){
$data = mysql_query("SELECT * FROM vehicles LIMIT 1", $conn);
$data_object = mysql_fetch_assoc($data);
// mysql_free_result($data);
}
mysql_close($conn);
$et = microtime(true) - $mt;
print "Elapsed time: " . number_format($et) . "s\n";
print "QPS: " . ($tq / $et) . "\n";
?> |
The next listing is the same code converted to leverage the mysqli_* calls. You’ll see that the result is not freed each time through the loop.
<?php
$tq = 1000000;
$mt = microtime(true);
$conn = mysqli_connect("127.0.0.1","user","","test_schema");
for($i=0; $i<$tq; $i++){
$data = $conn->query("SELECT * FROM vehicles LIMIT 1");
$data_obj = $data->fetch_assoc();
}
$conn->close();
$et = microtime(true) - $mt;
print "Elapsed time: " . number_format($et) . "s\n";
print "QPS: " . ($tq / $et) . "\n";
?> |
The results were surprising to me. The mysql_* extension actually ran considerably faster than the mysqli_* extenstion. In fact, the mysql_* extension ran 3.6% faster. Perhaps this DBA was on to something? I also tested this same methodology using prepared statements with MySQLi and found the results actually degraded. Discouraged, I devised a new test that would hopefully be more realistic. The values provided are queries per second.
| Method |
Run #1 |
Run #2 |
Run #3 |
Avg |
| mysql_* |
7,227.7 |
7,222.2 |
7,220.7 |
7,223.5 |
| mysqli_* |
6,963.0 |
6,974.0 |
6,974.4 |
6,970.5 |
| mysqli prepared statements |
4,849.6 |
5,005.3 |
4,978.7 |
4,944.5 |
Parameterized Queries:
More often than not, we do not run the same select query 1 million times, and we are actually more likely to run a query over a series of data sets. While this in itself is a terrible idea in production code because you should re-write the query to do it all as a single database call, it provides a better picture of how the database might be interacted with in PHP; especially if persistent connections are being utilized. However, if you find that you have some code that fits the patterns used here, it might be to your advantage to review the prepared statement code below.
The basic flow of the scripts is to first fetch 10 thousand (~1/3) of the table’s primary keys and populate an array. Then the code will select 1 million rows using a randomly selected primary key (vin). The data is still relatively small, and fits into the query cache after a warm up period. This shows how efficiently the query passing is instead of how efficient MySQL is at answering different questions, making it a better comparison of the two technologies. The three test scripts I ran are provided here for your reference.
Leveraging the mysql_* calls
<?php
print "Fetching VIN array...\n";
$vin_array = array();
$conn = mysql_connect("127.0.0.1","user","");
mysql_select_db("test_schema",$conn);
$vins = mysql_query("SELECT vin FROM vehicles LIMIT 10000", $conn);
while($vin = mysql_fetch_assoc($vins)){
$vin_array[] = $vin['vin'];
}
mysql_close($conn);
unset($conn);
print "Starting test...\n";
$tq = 1000000;
$mt = microtime(true);
$conn = mysql_connect("127.0.0.1","user","");
mysql_select_db("test_schema", $conn);
for($i=0; $i<$tq; $i++){
$data = mysql_query("SELECT * FROM vehicles WHERE vin = '" . $vin_array[rand(0,count($vin_array)-1)] . "'", $conn);
$data_object = mysql_fetch_assoc($data);
// mysql_free_result($data);
}
mysql_close($conn);
$et = microtime(true) - $mt;
print "Elapsed time: " . number_format($et) . "s\n";
print "QPS: " . ($tq / $et) . "\n";
?> |
Leveraging the mysqli_* calls
<?php
print "Fetching VIN Array...\n";
$vin_array = array();
$conn = mysqli_connect("127.0.0.1","user","","test_schema");
$vins = $conn->query("SELECT vin FROM vehicles LIMIT 10000");
while($vin = $vins->fetch_assoc()){
$vin_array[] = $vin['vin'];
}
$conn->close();
unset($conn);
print "Starting test...\n";
$tq = 1000000;
$mt = microtime(true);
$conn = mysqli_connect("127.0.0.1","user","","test_schema");
for($i=0; $i<$tq; $i++){
$data = $conn->query("SELECT * FROM vehicles WHERE vin = '" . $vin_array[rand(0,count($vin_array)-1)] . "'");
$data_obj = $data->fetch_assoc();
}
$conn->close();
$et = microtime(true) - $mt;
print "Elapsed time: " . number_format($et) . "s\n";
print "QPS: " . ($tq / $et) . "\n";
?> |
Leveraging mysqli_ calls and prepared statements.
<?php
print "Fetching VIN array...\n";
$vin_array = array();
$conn = mysqli_connect("127.0.0.1","user","","test_schema");
$vins = $conn->query("SELECT vin FROM vehicles LIMIT 10000");
while($vin = $vins->fetch_assoc()){
$vin_array[] = $vin['vin'];
}
$conn->close();
unset($conn);
print "Running tests...\n";
$tq = 1000000;
$mt = microtime(true);
$conn = mysqli_connect("127.0.0.1","user","","test_schema");
$stmt = $conn->stmt_init();
$stmt->prepare("SELECT * FROM vehicles WHERE VIN = ?");
for($i=0; $i<$tq; $i++){
$stmt->bind_param("s", $vin_array[rand(0,count($vin_array) - 1)]);
$stmt->execute();
$stmt->bind_result($a,$b,$c,$d,$e,$f,$g,$h,$z,$j,$k,$l,$m,$n);
$stmt->fetch();
}
$conn->close();
$et = microtime(true) - $mt;
print "Elapsed time: " . number_format($et) . "s\n";
print "QPS: " . ($tq / $et) . "\n";
?> |
The performance of this “harder” task degraded for all technologies. Again, the mysql_* extension was about 3.6% faster, but the prepared statements were 9.7% faster than the old mysql_* calls and 13.7% faster than the mysqli_ calls. This improvement makes a good case for transitioning repetitive calls in your code to prepared statements leveraging the MySQLi extenstions. Again, the values are measured in queries per second.
| Method |
Run #1 |
Run #2 |
Run #3 |
Avg |
| mysql_* |
4,063.6 |
4,016.7 |
4,054.0 |
4,044.8 |
| mysqli_* |
3,902.3 |
3,905.8 |
3,896.0 |
3,901.4 |
| mysqli prepared statements |
4,443.8 |
4,421.8 |
4,445.7 |
4,437.1 |
Without testing stored procedures, it’s difficult to say if they would have continued to improve the performance of this test, however, it is clear that the speed advantages of mysqli_* extensions are only present if you are utilizing the advanced features of the more recent versions of the database.
Conclusion:
If your code is purely hard coded statements, the old school MySQL syntax may be your best bet for a performance lift. However, most likely your code would benefit more from prepared statements and some of the other performance enhancements provided by the MySQLi extension set. I wouldn’t say, “MySQLi Sucks” but it does appear there is a valid argument for continuing to use it if you are not leveraging any of the performance enhancements of the newer versions of MySQL.
System Configuration:
- Mac OS 10.5.6
- 2.16Ghz Intel Core Duo
- 2 Gb RAM
- PHP 5.2.6
- MySQL Server 5.0.51b
JSON (also known as JavaScript Object Notation) is a handy way to serialize data for passing through mediums that do not recognized complex data types – such as HTTP. While using it to pass data today I was bit by a gotcha in how associative array data is encoded and decoded.
Following up on the post about 
I really like the date strings in the Facebook Newsfeed. They somehow make the data feel more personal when they relate to me with more natural language “Today”. This in place of callous standardized formatting so many websites present such as “2/10/2009″ or worse yet “02/10/2004″ or even worse still “2009-02-10″. The following code illustrates how you can create a quick and easy formatting of data that’s ordered by date and output it in a meaningful way to your users. You can see an example of this in action on the 
In the last post, I explained my work around to get data out of a Microsoft SQL Server using stored procedures. Since then, I’ve continued learning more about the limitations of the FreeTDS driver in PHP and hit another snafu when working with the data. Fields that are exported as VARCHAR are truncated at 256 characters! Whoa! big gotcha when today’s database tables might contain values in VARCHAR(1024) or even bigger! So what’s a PHP script to do? CAST the values!!!