I’ve just spent a couple of days optimising some of the database code on Carsurvey.org. Performance wasn’t becoming an significant issue, but I had bee in my bonnet about some of the existing code, where I knew there were some simple improvements I could implement. Specifically:
Replacing lots of nasty PHP mysql_result and mysql_num_rows calls, with more efficient mysql_fetch_array loops.
Using the slow query log with its log-queries-not-using-indexes option, to find some a couple of stray queries that were doing full table scans, which were quickly addressed by adding suitable indices.
Hi,
Interesting! – Are you saying that a while loop on mysql_fetch_array with a counter in the middle is faster than mysql_num_rows?
I’m very surprised by that…
Did you read that somewhere or just test it?
Ian
Hi Ian,
Often you just need to process all the results of a query, without having to know beforehand how many rows you have. In that case, the mysql_num_rows is pointless, as mysql_fetch_array will automatically return false when there are no more results. I suspect that even with a counter, it’s less overhead than asking MySQL for the number of rows, with all the associated communication overhead, unless your results set is very large.
Using mysql_result does a seek within a results set for every single field and row. And there’s lots of communcations overhead. Whereas with mysql_fetch_array, it’s just one row after another, with no need to jump back and forward in the results set.
The MySQL manual recommends avoiding mysql_result in favour of the row based alternatives (such mysql_fetch_array). I’ve also read similar recommendations elsewhere.
http://uk2.php.net/function.mysql-result
I’m using mysql_fetch_array, because I much prefer working with named fields, rather than numbered fields.
I haven’t done any detailed tests, but things seem to be running significantly more quickly, although having made quite a few changes, it’s difficult to be specific about which ones made the most difference. I suspect the change from mysql_result to mysql_fetch_array was more than important than removing a mysql_num_results calls.
Hi,
I always use while ($rsArray = mysql_fetch_array($qidQuery)) to loop through the result set, but i’ve never thought of mysql_num_rows as inefficient…
Ian
I’m now doing things the way you do, but for a long time I was coding PHP circa 1999, which wasn’t good. At least I got rid of the old default register globals many years ago 🙂
My process is to run all my queries through ‘explain’ to make sure they are all at least using indexes, and checking as few rows as possible.
I did once find a colleague’s statement with a subquery, where the subquery was checking about 5000 rows, and the main query was checking about 20,000 rows. A quick rewrite with a correct join and some decent indexes and I got it down to checking less than 100 rows (and that query was called on every page, eek).
… forgot to say, my preference is mysql_fetch_assoc, much better and cleaner to see the field names in the associative array. Has anyone seen any speed comparisons for these functions? I’m sure it doesn’t actually make much difference as the most popular queries will be cached by mysql anyway, but I suppose it could make a difference on a heavily loaded system.
Hi Gary,
I’ve been using explain to see what indices are being used. It took me a while to get my head around the results displayed by explain, but it is very useful information.
mysql_fetch_array is a superset of mysql_fetch_assoc, according the PHP manual. For that reason, I’d gone with mysql_fetch_array over mysql_fetch_assoc.
Interestingly, I just found this comment that suggests that there is a significant performance difference between mysql_fetch_assoc and mysql_fetch_array. I might have to rewrite some of my mysql_fetch_array calls. It’s not worth it for most pages, but I do have some pages where several hundred rows are retrieved, and the difference there could be worth it.
Anyway, both mysql_fetch_assoc and mysql_fetch_array are going to be far better than using my old friend mysql_result