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
The stable of services available through AWS is continuing to expand! Last night Amazon announced RDS (Relational Database Service) which look a lot like EC2 instances running MySQL with EBS volumes – something I have a fair bit of experience with. However, these have the added benefit of being a service that can scale memory and processor both up and down with a single service call.
Problem: We are given a large MySQL database table that no longer fits in your system’s working memory. You need to prune the data since a significant portion of this data is no longer relevant to keep in this table. Our expectation is ~75% of the data will remain in the table because of a uniform and random distribution of values in col1 and col2. How then, do we go about pruning this table as efficiently as possible? 
Database servers are tricky. Before jumping to the conclusion that more hardware is needed, make sure you’re getting the most out of the hardware you already have. Hiring a DBA is definitely the right way to go. They’ll quickly be able to point out where the pain points are in your configuration. If you can’t afford a DBA, you can use this quick guide to get a starting point on what might need adjustment.
I was struggling with MySQL Query Browser, trying to make some minor modifications to a schema recently when a friend, aptly named Erik, from 