PHP: mysql or mysqli?

Jacob Allred
#my-sites#web-dev

As you may know, I’ve been spending a lot of time trying to make the Fake Name Generator as fast as possible. I’ve started using a CDN, I’ve made minor code tweaks (like doing ++$i instead of $i++), added APC caching, and a bunch of other stuff, but still feel that the core of the FNG is a bit slow.

Currently the code uses PHP’s mysql extension. I’ve read in several places that mysqli is better, faster, more attractive, gets more girls, etc, but have been unable to find any remotely recent benchmarks to prove it. I don’t care if the OO approach is cleaner looking, I just want it to be faster.

So I put together a bit of a benchmark (very unscientific) to see if it is faster or not for my very specific needs: I want to generate 50,000 names as fast as possible. Each name requires 4 SQL queries, so I end up making 200,000 queries (yikes!).

For my first attempt, I simply replaced all of my procedural mysql stuff with the OO mysqli equivalent. In other words, I connected to the database, ran each query, then got the result out of the result set. This had absolutely no improvement over my current method of getting data; sometimes mysql was a second or two faster, sometimes mysqli was a second or two faster.

For my second attempt, I used the mysqli::multi_query method to combine the 4 SQL queries per name down to a single trip to the database per name, dropping my total number of trips to the database from 200,000 to 50,000. This yielded a drastic 66% speed improvement. There are several methods in my name generation class that generate multiple queries, so using this technique will make the name generator significantly faster.

Conclusion: As a drop-in replacement for the standard mysql extension, mysqli does not appear to be any faster, even for lots of queries. However, the mysqli::multi_query method makes it possible to shave large percentages of time off of your script’s execution time.

Update (2-4-2010): I just ran an order for 10,000,000 names (with addresses, occupations, etc). This process used to take about 25 hours to run on my server. With the new code it ran in only 15 hours. That is a 40% speed improvement for about 45 minutes of effort! So my conclusion is yes, mysqli can be much faster in certain situations.