MySQL 15% faster with Simple Function Change

[Frank_Rizzo]

After using php and mysql for 10+ years I thought I knew every optimisation trick there is.

But yesterday I found something I had never heard of and using it made mysql / php reports run 15% faster! Reports that took 12 seconds now take 10, 6 seconds now take 5.

The function is

mysql_unbuffered_query()

Using it did give a significant boost to my code. And it's so simple to use - just replace (some) existing mysql_query() functions with mysql_unbuffered_query()

It will not work in all situations and there are some disadvantages. But if you tend to write code which looks similar to this:

$result = mysql_unbuffered_query("SELECT lots_of_fields from big_table" );

while ($row = @mysql_fetch_assoc($result)) {
 //loop through processing and displaying data
}

Then you too will see a big change in performance.

Advantages
Big boost in performance because data available immediately (not buffered)
Uses less memory (only one row a time is stored in RAM)

Disadvantages
You have to read all the data and cannot issue another select within the loop
You can not use mysql_num_rows to determine how many rows returned
Table will be locked whilst you loop through

Some of the disadvantages can be overcome but this function works best if you have static tables, which are not updated often.

I copy my tables to ENGINE=MEMORY and run them from there. That is the fastest way to read MyISAM tables. By now using mysql_unbuffered_select() I got an extra 15% out of it.

It's amazing that after all these years you discover something in the attic that you never knew about, but which makes such a change!

[eelixduppy]

Thanks for sharing your results!

Reports that took 12 seconds now take 10, 6 seconds now take 5.

That is awfully a lot of time to begin with; it wouldn't surprise me that there are other things that need to be optimized here to make these run faster.

[brotherhood of LAN]

Good shout.

Basically if you don't need to do another query while dealing with the initial query data, then use unbuffered.

It's vital to use when you have a massive table, you can do a SELECT * from a billion row table and start dealing with the rows instantly rather than unnecessarily waiting for MySQL to load the load into memory... and then sending it to your client.

[Frank_Rizzo]

What amazes most is that I never knew it existed. Found it by chance looking for something else.

That is awfully a lot of time to begin with;

Two years the biggest report took about 25 seconds. Last week it was 16 seconds. Latest hardware upgrade got it down to 12. Now this function change shaved another 2 seconds off it.

I don't think they can be generated any quicker. The mysql stuff takes about 3 seconds, the php parsing of the data and formatting the output tables takes about 7 seconds.