When dealing with extremely large datasets, it becomes absolutely vital to performance that the results be paginated. Basically this means splitting up a result set from potentially thousands of records, into more manageable chunks - or pages - that limit the query to a targeted set of rows thereby improving performance and lowering system overhead.

This presents some issues when formulating your queries, as you *never* want to do a mysql_num_rows on a result set as your means of determining the number of rows as this runs the entire query, buffers it, and THEN returns the value. Furthermore this is rendered useless if your initial query contains the "limit" directive, as it's only ever going to return the number of rows from the result set.

For a query that has a lot of records, this process can be very performance intensive. This means that you're forced to write TWO queries; One to get the maximum number of records with a simple count directive (so we know how to split up the paging), and then ANOTHER one to actually fetch the data with the "limit" directive in MySQL. Consider this query:

To get the max rows:

select count(*)
from accounts inner join accountData on accountData.acctID = accounts.id
where accounts.inactive=0 and accountData.someField = someValue

This will return to you the total number of rows (by just using the count directive) in a single record - this is very fast in MySQL provided you have indexes properly configured for the tables you're joining. This ALSO means that once you HAVE the total count, that you need to write ANOTHER query to get the data you actually WANT which is then limited to a "chunk" as defined by the limit directive. This would be our final query:

select accounts.firstName, accounts.lastName, acccounts.email, accounts.etc, accountData.someField, accountData.someField2
from accounts inner join accountData on accountData.acctID = accounts.id
where accounts.inactive=0 and accountData.someField = someValue
order by accounts.firstName asc
limit 0, 25

Basically this second query pulls in the columns we want, orders the query as desired, and then limits the resultset to 25 records starting from record 0.

This is all fine and well, however we have now written TWO Queries to run MySQL, even though the first one is a simple count. Now I don't know about you, but I get VERY tired of trying to make tricky ways to only write a query ONCE in code... tricky ways like splitting up the segments and then pasting together for the two queries, etc. The simple example above means you're literally writing two different queries - and if one has to change, so does the other - which lends itself to errors in future development. The answer to this, is to use a simple wrapper function to return to you the number of rows from a fully written query using regular expressions to parse out the excess elements we don't need for the count itself.

Consider this function:

function getTotalCount($qry)
{
    return mysql_fetch_assoc(preg_replace(array("/select(.*)from/is","/limit [0-9]+(\s?\,\s?[0-9]+)?/is"),array("select count(*) from",""), $qry));
}

Basically, in short, this function parses a fully written query and removes the "limit" directive, and replaces ANY columns in the select clause with a simple "count(*)" statement. It could be further tweaked to remove the "order by" clause as well if you were so inclined to completely maximize performance.

Using this function, we can now write a SINGLE query, get the max number of records from it without altering in code, and then immediately execute the same full query to then traverse the result set afterward.

Hope this helps someone else!