PHP: mysql or mysqli?

As you may know, I’ve been spend­ing a lot of time try­ing to make the Fake Name Gen­er­a­tor as fast as pos­si­ble. 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.

Cur­rently the code uses PHP’s mysql exten­sion. I’ve read in sev­eral places that mysqli is bet­ter, faster, more attrac­tive, gets more girls, etc, but have been unable to find any remotely recent bench­marks to prove it. I don’t care if the OO approach is cleaner look­ing, I just want it to be faster.

So I put together a bit of a bench­mark (very unsci­en­tific) to see if it is faster or not for my very spe­cific needs: I want to gen­er­ate 50,000 names as fast as pos­si­ble. Each name requires 4 SQL queries, so I end up mak­ing 200,000 queries (yikes!).

For my first attempt, I sim­ply replaced all of my pro­ce­dural mysql stuff with the OO mysqli equiv­a­lent. In other words, I con­nected to the data­base, ran each query, then got the result out of the result set. This had absolutely no improve­ment over my cur­rent method of get­ting data; some­times mysql was a sec­ond or two faster, some­times mysqli was a sec­ond or two faster.

For my sec­ond attempt, I used the mysqli::multi_query method to com­bine the 4 SQL queries per name down to a sin­gle trip to the data­base per name, drop­ping my total num­ber of trips to the data­base from 200,000 to 50,000. This yielded a dras­tic 66% speed improve­ment. There are sev­eral meth­ods in my name gen­er­a­tion class that gen­er­ate mul­ti­ple queries, so using this tech­nique will make the name gen­er­a­tor sig­nif­i­cantly faster.

Con­clu­sion: As a drop-in replace­ment for the stan­dard mysql exten­sion, mysqli does not appear to be any faster, even for lots of queries. How­ever, the mysqli::multi_query method makes it pos­si­ble to shave large per­cent­ages of time off of your script’s exe­cu­tion time.

Update (2−4−2010): I just ran an order for 10,000,000 names (with addresses, occu­pa­tions, 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 improve­ment for about 45 min­utes of effort! So my con­clu­sion is yes, mysqli can be much faster in cer­tain situations.