Seek a specific record in MySQL paged results

Question

I've a classic pagination system using LIMIT startrecord, endrecord and I want to figure out in what page number an X record is located.

The only idea I've right now is to seek recursively all the records to find it out. But I'm looking for a much more "economic" method!

Any ideas ?

Answer 1

You could use a sub query to create a table with the results and their position, then query that for the specific entry you are looking at:

SET @rank=0; 
SELECT rank, record 
FROM (
    SELECT 
        @rank:=@rank+1 AS rank, 
        record 
    FROM table
) as subquery 
WHERE record = x;

The returned table would show the record an the rank it appeared in the original query. You can the divide the rank by the number of results per page... Or build it into the query. Hope this helps.