Query Advisor Results

[gms8994]

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”.

[xaprb]

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/

[gms8994]

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