Yes, it can be done in MySQL:
http://forums.mysql.com/read.php?125,246731 (Alternative to FOUND_ROWS())
http://forums.mysql.com/read.php?10,243777 (Query performance question (using index slows down?))
http://forums.mysql.com/read.php?10,239876 (Query performance on database with joins)
http://forums.mysql.com/read.php?10,360578 (join in union)
http://forums.mysql.com/read.php?115,355941 (Query that returns indexed fields involves FILESORT, why?)
http://forums.mysql.com/read.php?24,291646 (Very Slow Simple Query)
http://forums.mysql.com/read.php?10,292296 (break mysql query? Is this possible in my sql)
http://forums.mysql.com/read.php?24,240838 (Slow search query)
http://forums.mysql.com/read.php?24,234339 (MySQL Performance Tuning on my server)
The idea is to remember where you "left off", then do
WHERE id > @left_off LIMIT 10
Whether to do > or >= ? That gets into the details. Also I like to do LIMIT 11 for 10 items, plus discovering whether there is a "Next" page.
This technique also avoids the pseudo-bug that happens when an item is DELETEd or INSERTed while the user is reaching for the Next button. With LIMIT N,M an item could be skipped or duplicated.
A temporary table is unscalable in multiple ways:
* What if there are a million items? It will take an awful amount of time to build.
* Where will you put the may table for your many lists?
* When do you drop the table?
Related topics:
* Displaying "Items 11-20 of 8765" -- This could use FOUND_ROWS(), but leads to scaling problems (as alluded to in some of those links)
* Next and Prev are easy. First and Last take some extra code, but are also easy. Jumping directly to Page 123 should not be allowed in your UI. OTOH, jumping to any of the Next/Prev 5 pages is not costly, but takes some more design work.
* If the items are ordered by a single column, all is nice. If there are multiple columns or there is filtering (eg, WHERE hidden=0 AND published=1), the performance may suffer.
* If you have to JOIN two tables to do the filtering -- Well, this may totally destroys any hope of performance.
Show me your specifics (SHOW CREATE TABLE, SELECT, etc) and I may have more pointers.