I'd like to know if anyone has any first-hand experience with this dichotomy. A few blogs say the mysql extension is faster than mysqli. Is this true?
And I'm only asking about speed. I know mysqli has features that are not present in the older extension.
The MySQL extension is very slightly faster than MySQLi in most benchmarks I've seen reported. The difference is so slight, however, that this should probably not be your criterion for deciding between the two.
Other factors dwarf the difference in performance between mysql and mysqli. Using mod_php or FastCGI, a bytecode cache like APC, or using data caching judiciously to reduce database hits, are far more beneficial for overall performance of PHP scripts than the choice of MySQL extension.
Don't be penny wise and pound foolish! :-)
"It depends."
For example, PHP MySQL vs MySQLi Database Access Metrics and the subsequent comments point out arguments both ways.
If you have a mature database and codebase, do some testing and see what works in your system. If not, stop worrying about premature optimization.
See http://php.net/manual/en/mysqlinfo.api.choosing.php
The overall performance of all three extensions is considered to be about the same. Although the performance of the extension contributes only a fraction of the total run time of a PHP web request. Often, the impact is as low as 0.1%.
According to all the Google results for benchmarks linked by ceejayoz it looks like MySQL is at least slightly faster than MySQLi in all the benchmark tests. I do recommend reading the results for details but just figured I'd post something that directly answered the question and bumps up ceejayoz's answer.
Maybe, this can be a reason to make the right choice :: The Plot to Kill PHP MySQL Extension
" Yes, you read it right. Recently, Phillip Olson sent to the PHP internals mailing list a proposal to kill the original PHP MySQL extension in future PHP versions. "
The PHP documentation has a good comparison of mysql, mysqli, and PDO. I know you only asked about speed, but others might find this useful. It talks about the feature differences between the options.
In relation to PHP programming language, MySQL is the old database driver, and MySQLi is the Improved driver. MySQLi takes advantage of the newer features of MySQL 5.
Features of MySQLi taken from php.net site:
Unless milliseconds matter then don't worry. Surely if you have no need for the extra functionality provided by mysqli then just stick with the tried and tested mysql.
<?php
$start = microtime();
$c = new mysqli('localhost', 'username', 'userpass', 'username_dbname');
$c -> select_db('username_dbname');
$q = $c -> query("SELECT * FROM example");
while ($r = $q -> fetch_array(MYSQLI_ASSOC))
{
echo $r['col1'] . "<br/>\n";
}
$me = $c -> query("SELECT col1 FROM example WHERE id='11'") -> fetch_array(MYSQLI_ASSOC);
echo $me['col1'];
echo (microtime() - $start);
?>
Why when using mysqli oop is there a slight speed increase from using this script with mysql or mysqli procedural style? When testing the above script I get .0009 seconds consistantly more than when using the other 2. When using mysql or mysqli procedural, I loaded the scripts 20x in each different style and the two were always above .001. I load the above script 20x and I get below .001 5x.
MySQLi has two basic advantages over MySQL; prepared statements are a great way to avoid SQL injection attacks. Secondly MySQL (or mariaDB) will do their best to optimize prepared statements and thus you have the potential for speed optimizations there. Speed increases from making the database happy will vastly outsize the tiny difference between MySQL and MySQLi.
If you are feeding in statements you mangle together yourself like SELECT * FROM users WHERE ID=$user_id the database will treat this as a unique statement with each new value of $user_id. But a prepared statement SELECT * FROM users WHERE ID=? stands a much better chance of having some optimizations/caching performed by the database.
But comparisons are fairly moot as MySQL is now officially deprecated. From the horse's mouth:
Deprecated features in PHP 5.5.x
ext/mysql deprecation
The original MySQL extension is now deprecated, and will generate E_DEPRECATED errors when connecting to a database. Instead, use the MySQLi or PDO_MySQL extensions.
