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/