Jim Westergren
A blog about me, my projects, SEO, Web Development and Personal Development.
"If we did all the things we are capable of, we would literally astound ourselves." - Thomas A. Edison

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

Comments powered by Disqus
About the Author Jim Westergren Jim Westergren is a Swedish web entrepreneur. He is happily married and has two lovely children. Some of his interests are web development, SEO and writing. He is the Founder and owner of TodaysWeb offering SEO, web development and manages projects such as DomainStats and N.nu. Read his ..

Design, text and CMS (N.nu) by myself.