Everyone else probably already knows this, but $project is/was doing two queries on the MySQL database every time the end user typed in something to search on
- to get the data between a set range (SELECT x,y….. LIMIT n, OFFSET m or whatever) and
- another to get the total count of records (SELECT count(field) ….).
This is all very good, until there is sufficiently different logic in each query that when I deliberately set the offset in query #1 to 0 and limit very high and find that the of rows returned by both doesn’t match (this leads to broken paging for example)
Then I thought – surely everyone else doesn’t do a count query and then repeat it for the range of data they want back – there must be a better way… mustn’t there?
At which point I found:
http://forge.mysql.com/wiki/Top10SQLPerformanceTips
and
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
See also the comment at the bottom of http://php.net/manual/en/pdostatement.rowcount.php which gives a good enough example (Search for SQL_CALC_FOUND_ROWS)
A few modifications later, run unit tests… they all pass…. all good.
I also found some interesting code like :
$total = sizeof($blah);
if($total == 0) { … }
elseif ($total != 0) { …. }
elseif ($something) { // WTF? }
else { // WTF? }
(The WTF comment were added by me… and I did check that I wasn’t just stupidly tired and not understanding what was going on).
The joys of software maintenance.