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

Well, hopefully now it will.

bookmarked scans
bookmarked scans
bookmarked scans
bookmarked scans
bookmarked scans
bookmarked scans

Whee!