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”.
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: [URL]http://www.percona.com/live/santa-clara-2009/[/URL]