PDO vs MySQLi speed comparison

My conclusion

Based on my testing and the advantages of PDO (info) I recommend the following:

Use PDO with PDO::ATTR_EMULATE_PREPARES set to TRUE and use the charset=utf8mb4 in the dsn (to be equally safe as if emulation was turned off).

The speed increase is around 80% and this is not the default with for example Laravel.

The difference with MySQLi with using real_escape_string is really so small that it is not worth it (around 1.5%) and using prepared statements is much safer.

I have MySQL on a separete server (which is very common) and I need to be able to execute thousands of queries per second (perhaps not so common) so this is a big deal for me. I am testing here a common select query in my production environment.

My test results

Test with short latency (using the web server connecting to external MySQL server in the same datacenter with PHP Version 5.6) using 5000 simple queries:

test 1 (PDO with query + quote) finished in 4.282 seconds

test 2 (PDO with prepared statement) finished in 4.465 seconds

test 3 (PDO with emulation with query + quote) finished in 2.578 seconds

test 4 (PDO with emulation with prepared statement) finished in 2.518 seconds

test 5 (MySQLi with real_escape_string) finished in 2.482 seconds

test 6 (MySQLi with prepared statement) finished in 4.396 seconds

Test with long latency (using localhost connecting to external MySQL server with PHP Version 7.1) using 100 simple queries:

test 1 (PDO with query + quote) finished in 6.231 seconds

test 2 (PDO with prepared statement) finished in 6.489 seconds

test 3 (PDO with emulation with query + quote) finished in 3.126 seconds

test 4 (PDO with emulation with prepared statement) finished in 3.127 seconds

test 5 (MySQLi with real_escape_string) finished in 3.123 seconds

test 6 (MySQLi with prepared statement) finished in 6.189 seconds

Run your own test

Thanks to colshrapnel on reddit for clarifying so that I could update this article.

31 Oct 2017

Other posts