Efficient algorithm for Next button on a MySQL result set

Question

I have a website that lets people view rows in a table (each row is a picture). There are more than 100,000 rows. You can view different subsets of the rows, and you can view them with different sort orders. While you are viewing one of the rows, you can click the "Next" or "Previous" buttons to go the next/previous row in the list.

How would you implement the "Next" and "Previous" features of the website?

More specifically, if you have an arbitrary query that returns a list of up to 100,000+ rows, and you know some information about the current row someone is viewing, how do you determine the NEXT row efficiently?

Here is the pseudo-code of the solution I came up with when the website was young, and it worked well when there were only 1000 rows, but now that there are 100,000 rows I think it is eating up too much memory.

int nextRowId(string query, int currentRowId)
{
    array allRowIds = mysql_query(query);  // Takes up a lot of memory!
    int currentIndex = (index of currentRowId in allRowIds);  // Takes time!
    return allRowIds[currentIndex+1];
}

While you are thinking about this problem, remember that the website can store more information about the current row than just its ID (for example, the position of the current row in the result set), and this information can be used as a hint to help determine the ID of the next row.

Edit: Sorry for not mentioning this earlier, but this isn't just a static website: rows can often be added to the list, and rows can be re-ordered in the list. (Much rarer, rows can be removed from the list.) I think that I should worry about that kind of thing, but maybe you can convince me otherwise.

Answer 1

USe the mysql limit clause. as in select * from tableA limit 0,100;

You parametereize the 0 and 100 naturally, store them in a form or whereever you need, so you'd know the offset to use when the user hits Next. That'll require you to rerun the query for every next/prev page you render.

Answer 2

Have you tried applying the LIMIT-clause to the query?

From the MySQL manual

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

Answer 3

Offset is a very expensive operation on large data sets, check out this presentation for more info about efficient paging in mySQL: http://www.scribd.com/doc/14683263/Efficient-Pagination-Using-MySQL