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.
Other posts
- My Recommendations for a Happy Life
- Budget 100 - an old school magic format
- My Favorite Board Games and How I Play Them
- Switching from Windows PC to Mac and why I switched back
- Creating The Space War - The Card Game of My Dreams
- 24 Characteristics That Geniuses Have in Common
- Setting up and Managing a MySQL Server
- Canasta - The Great Card Game
- Annual report number 13 + 14: My Success
- Selling my SEO business TodaysWeb
Jim Westergren is a Swedish web entrepreneur currently living in Spain. He is happily married and has three lovely children. Some of his interests are web development, SEO and writing.He is the Founder of DomainStats and N.nu. Read his full about page.