Which is fastest in PHP- MySQL or MySQLi?

Question

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.

Answer 1

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%.

Answer 2

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.

Answer 3

"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.

Answer 4

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.

Answer 5

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! :-)

Answer 6

<?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.