PDO vs MySQLi speed comparison | Jim Westergren
source link: https://www.jimwestergren.com/pdo-versus-mysqli
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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
He is the Founder of DomainStats and N.nu. Read his full about page.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK