Pagination with MySQL and FOUND_ROWS()

Jody clued me into a convenient method of paginating a result set from MySQL. Since he hasn’t blogged about it yet, I will >:-)

In the past, I’d issue two queries back to back, similar to this:

    $page = isset($_GET['page']) ? (int) $_GET['page']: 1;
    $size = 10;
    $offset = ($page - 1) * $size;
    $db = new mysqli("localhost", "drew", "mypass", "db");
    $sql = "SELECT username FROM user LIMIT $offset, $size;"
         . "SELECT COUNT(*) FROM user";
    $users = array();
    $total = 0;
    if ($db->multi_query($sql) && $res = $db->store_result())
        // fetch this page of user records
        while (list($name) = $res->fetch_row())
            $users[] = $name;
        // fetch the TOTAL number of users
        list($total) = $db->store_result()->fetch_row();
    $pages = ceil($total/$size);
    echo "Total Users: $total<br>";
    echo "Total Pages: $pages<br>";
    echo "Current Page: $page<br><br>";
    foreach ($users as $u) echo "$u<br>";
    if ($page > 1) 
        echo '<a href="?page=', $page-1, '">&laquo; Prev</a>';
    if ($page > 1 && $page < $pages) 
        echo " | ";
    if ($page < $pages) 
        echo '<a href="?page=', $page+1, '">Next &raquo;</a>';

Take note of Line #8. The first query grabs the specified page of results, the second simply does a full count of all rows so I could calculate the total number of pages.

What sucks, however, is if the query is more complex, especially if the various parts of it have to be dynamically generated, e.g., table joins, conditions in the where clause, etc. In such a case you’d have to change the second query to get rid of the column list SELECT column1, column 2… and make it SELECT COUNT(*). Also, you’d have to toss out any ORDER BY and LIMIT clauses.

MySQL allows you to simplify this extremely common scenario by using the FOUND_ROWS() function. In the above code, you can simply modify the query itself:

    // ... change this:
    $sql = "SELECT username FROM user LIMIT $offset, $size;"
         . "SELECT COUNT(*) FROM user";
    // ... to this:
    $sql = "SELECT SQL_CALC_FOUND_ROWS username FROM user LIMIT $offset, $size;"
         . "SELECT FOUND_ROWS()";

The rest of the code remains the same and will give you the exact same results.

The speed of this approach can vary depending on how you have your table(s) indexed, and the complexity of the query itself. In other words, in some circumstances it may be faster to actually run the LIMITed query followed by the appropriate SELECT COUNT(*) FROM … query as in the first example, but the primary benefit is in the simplification of the query itself.