Query Advisor Results

Per Baron, I’m pasting this here:

http://tools.percona.com/query/PLUQcFBa/advice

specifically, I can’t seem to find any information on CLA.003 (Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger. Pagination techniques such as bookmarked scans are much more efficient.) and “bookmarked scans”.

The reason it’s warning about CLA.003 is this:

LIMIT 20 OFFSET 7620

This means that the query will actually generate 7620 + 20 rows, and then throw away the first 7620. That is, 99.74% of the work it’s doing is wasted.

A bookmarked scan works like this: select the first 21 results, display 20, and remember the 21st result. Next time, use a greater-than or less-than with the 21st result as the starting point and a LIMIT 21 again. To generate the third page, start from the 41st row, and so on. This way the query is only generating 20 rows at a time.

However, this won’t help in your case, because you have a GROUP BY, and some ORDER BY problems that will prevent early materialization. This query will always generate a temporary table with ALL the rows, sort them, then scan them and retrieve the desired rows.

This is a very common type of problem in applications that show paginated displays. There are a variety of ways to solve it; I’d begin with the slides for “Efficient Pagination Using MySQL” from our 2009 conference, listed here: http://www.percona.com/live/santa-clara-2009/

Can I suggest linking to that slide deck from that warning? Googling for “bookmarked scans” doesn’t work very well…