I was having similar thoughts recently and my solution was this:
I first store my FROM clause in a variable and construct my WHERE clause and store that in another variable.
Then I calculate the total rows by selecting just the ID field using the FROM and WHERE clauses:
$intRecFirst = isset($_GET["FirstRecord"]) ? funcStripText($_GET["FirstRecord"]) : 0; //First Row
$intRecTotal = isset($_GET["TotalRecords"]) ? funcStripText($_GET["TotalRecords"]) : 0; //Total Records
$intRecPage = 20; // The mumber of records per page
.....
if ($intRecTotal == 0){
$pdo = $connDB->prepare("SELECT ID FROM" . $strSQLFROM . $strSQLWHERE); // SELECT just the ID field
$pdo->execute();
$intRecTotal = $pdo->rowCount();
$pdo->closeCursor();
}
I tried SELECT COUNT(ID), but found that using rowCount() was marginally faster.
Also, I'm only doing this when the page is first loaded, or if the filter/search parameters change, thereafter I pass $intRecTotal along with the URL paramaters, for example:
echo '<a href="Page.php?FirstRecord=' . $intRecFirst . '&TotalRecords=' . $intRecTotal . '">Next Page</a>'
Then I'm re-using the FROM and WHERE clauses in the SELECT statement that retrieves the data I actually want to display on the page:
$pdo = $connDB->prepare("SELECT ID, Something, SomethingElse FROM " . $strSQLFROM . $strSQLWHERE . " LIMIT " . $intRecFirst . "," . $intRecPage . ";");
$pdo->execute();
$arrView = $pdo->fetchAll();
$pdo->closeCursor();
$connDB = null;
If there are a really large number of records, you could perhaps extend this to put a limit on first SELECT statement as well, because if we have 20 records per page we don't really expect someone to page through more than 20, 30 pages? Instead you'd expect them to use the search/filter to further narrow the results before paging through them, something like this:
if ($intRecTotal == 0){
$pdo = $connDB->prepare("SELECT ID FROM" . $strSQLFROM . $strSQLWHERE . " LIMIT 0, 1000"); // SELECT just the ID field
$pdo->execute();
$intRecTotal = $pdo->rowCount();
$pdo->closeCursor();
}
Then on the page instead of displaying something like "Records 1 to 20 of 123,456", we could show something like this: "Records 1 to 20 of the first 1,000".
What do you think?
I also came across this: http://www.percona.com/files/presentations/ppc2009/PPC2009_mysql_pagination.pdf, which talks about not using offset with LIMIT, but instead using the last ID to append to the WHERE clause. However, I can't see how you would use this with records that can be re-ordered so that IDs are no longer sequential.
