Need help optimizing MySQL query with joins

Question

I'm still having problems understanding how to read, understand and optimize MySQL explain. I know to create indices on orderby columns but that's about it. Therefore I am hoping you can help me tune this query:

EXPLAIN
SELECT specie.id, specie.commonname, specie.block_description, maximage.title,
       maximage.karma, imagefile.file_name, imagefile.width, imagefile.height,
       imagefile.transferred
FROM specie
INNER JOIN specie_map ON specie_map.specie_id = specie.id
INNER JOIN (
    SELECT *
    FROM image
    ORDER BY karma DESC
) AS maximage ON specie_map.image_id = maximage.id
INNER JOIN imagefile ON     imagefile.image_id = maximage.id
                        AND imagefile.type = 'small'
GROUP BY specie.commonname
ORDER BY commonname ASC
LIMIT 0 , 24 

What this query does is to find the photo with the most karma for a specie. You can see the result of this live:

http://www.jungledragon.com/species

I have a table of species, a table of images, a mapping table in between and an imagefile table, since there are multiple image files (formats) per image.

Explain output:

For the specie table, I have indices on its primary id and the field commonname. For the image table, I have indices on its id and karma field, and a few others not relevant to this question.

This query currently takes 0.8 to 1.1s which is too slow in my opinion. I have a suspicion that the right index will speed this up many times, but I don't know which one.

Answer 1

I think you'd go a great way by getting rid of the subquery. Look at the first and last rows of the "explain" result - it's copying the entire "image" table to a temporary table. You could obtain the same result by replacing the subquery with INNER JOIN image and moving ORDER BY karma DESC to the final ORDER BY clause:

SELECT specie.id, specie.commonname, specie.block_description, maximage.title,
       maximage.karma, imagefile.file_name, imagefile.width, imagefile.height,
       imagefile.transferred
FROM specie
INNER JOIN specie_map ON specie_map.specie_id = specie.id
INNER JOIN image AS maximage ON specie_map.image_id = maximage.id
INNER JOIN imagefile ON     imagefile.image_id = maximage.id
                        AND imagefile.type = 'small'
GROUP BY specie.commonname
ORDER BY commonname ASC, karma DESC
LIMIT 0 , 24