Using MySQL’s SQL_CALC_FOUND_ROWS to display paged data starting from the last page

MySQL’s FOUND_ROWS() and SQL_CALC_FOUND_ROWS let’s you issue a query that uses the LIMIT clause and at the same time, get the total number of rows matched by your query if the LIMIT clause weren’t there. This means that if you want to display pages of data and also want to show to the user the total number of pages you have, you don’t have to use two separate queries (first is for getting the total matching records, second is for fetching the records for the active page using a LIMIT clause).

Wanting to use this on my project (a messageboard), I was faced with the problem of displaying the last page of data first using this approach. Displaying the first page is trivial, but getting out the last page of data first requires some trickery. The main problem I had was I don’t know how many records I have in total so I can’t specify the correct offset in my LIMIT clause.

To better illustrate this, let’s create a simple example using the following MySQL table schema and sample data:

CREATE TABLE records (
  id int unsigned primary key auto_increment,
  content text
);

INSERT INTO records VALUES (1, 'Record 1'), (2, 'Record 2'), (3, 'Record 3'), (4, 'Record 4'), (5, 'Record 5'), (6, 'Record 6'), (7, 'Record 7'), (8, 'Record 8'), (9, 'Record 9'), (10, 'Record 10'),...(94, 'Record 94');

We have 94 records, and let’s say we want to show 10 records per page, starting from the last page. We should have 10 pages total, with the last page containing only 4 records. We want the last page with 4 records to show up first.

Let’s use the following query to fetch our last page of records using SQL_CALC_FOUND_ROWS:

$res1 = mysql_query( "SELECT SQL_CALC_FOUND_ROWS * FROM records ORDER BY id DESC LIMIT 10" );

Notice that even though we only want to show the 4 records on our last page, we still specified 10 in our LIMIT clause. This is because when issuing this query, we still don’t know the total number of records that match our query. We just assume for now that we have evenly distributed data. Remember that we have our last page results on $res1.

To get how many rows we have in total, we use the following code:

$res2 = mysql_query( "SELECT FOUND_ROWS() value" );
$temp = mysql_fetch_assoc( $res2 );
$total_records = $temp['value'];
mysql_free_result( $res2 );

Now we know how many records we have in total. To find out how many pages we should have, and how many records we should be showing in the last page:

$total_pages = ceil( $total / 10 );
$last_page_records = ( $total % 10 );

We now have our total page count ($total_pages) and we now know how many records we should be showing on our last page ($last_page_records). Now let’s get the records we need to display from $res1.

$data_array = array();
for ( $i = $last_page_records - 1; $i >= 0; --$i ) {
  mysql_data_seek( $res1, $i );
  $data_array[] = mysql_fetch_assoc( $res1 );
}

Since in our SELECT query earlier, we specified that data be sorted in reverse order (so we can get from the end of the matching records), we had to get our records in reverse order to get it back to normal sorting order.

A thing to note here is that we’re fetching data in reverse order. This means using ORDER BY attribute DESC. As of this writing, MySQL only stores index values in ascending order. This means you’ll be getting a Using filesort in your EXPLAIN results. To get around this, you can create pseudo reverse indexes, as suggested by Igvita.com.

Suggestions, corrections, criticisms and findings are welcome.