需要帮助优化连接的MySQL查询

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:

我在理解如何阅读，理解和优化MySQL解释时仍然遇到问题。我知道在orderby列上创建索引，但这就是它。因此，我希望你能帮助我调整这个查询：

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

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.

对于specie表，我的主要ID和字段commonname都有索引。对于图像表，我的id和karma字段有索引，还有一些与这个问题无关的索引。

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.

这个查询目前需要0.8到1.1秒，这在我看来太慢了。我怀疑正确的索引会加快这个速度，但我不知道哪一个。

3 个解决方案

#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:

我认为通过摆脱子查询你会走得很好。查看“explain”结果的第一行和最后一行 - 它将整个“image”表复制到临时表。您可以通过将子查询替换为INNER JOIN图像并将ORDER BY karma DESC移动到最终的ORDER BY子句来获得相同的结果：

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 

#2

The real problem is that there is no need to optimize MySQL explain. There is usually a query (or several queries) that you want to be efficient and EXPLAIN is a way to see if the execution of the query is going to happen as you expect it to happen.

真正的问题是没有必要优化MySQL解释。通常会有一个查询（或多个查询），您希望它是高效的，并且EXPLAIN是一种查看查询执行是否会按预期发生的方式。

That is you need to understand how the execution plan should look like and why and compare it with results of the EXPLAIN command. To understand how the plan is going to look like you should understand how indexes in MySQL work.

那就是你需要了解执行计划应该是什么样子以及为什么要将它与EXPLAIN命令的结果进行比较。要了解计划的外观，您应该了解MySQL中的索引是如何工作的。

In the meantime, your query is a tricky one, since for efficient index using it has some limitations: a) simultaneous ordering and by a field from one table, and b) finding the last element in each group from another (the latter is a tricky task as itself). Since your database is rather small, you are lucky that you current query is rather fast (though you consider it slow).

与此同时，您的查询是一个棘手的问题，因为对于使用它的高效索引有一些限制：a）同时排序和来自一个表的字段，以及b）从另一个表中找到每个组中的最后一个元素（后者是一个棘手的任务本身）。由于您的数据库相当小，您很幸运，您当前的查询速度相当快（尽管您认为它很慢）。

I would rewrite the query in a bit hacky manner (I assume that there is at least one foto for each specie):

我会以一种hacky方式重写查询（我假设每个物种至少有一个foto）：

SELECT
   specie.id, specie.commonname, specie.block_description,
   maximage.title, maximage.karma,
   imagefile.file_name, imagefile.width, imagefile.height, imagefile.transferred
FROM (
    SELECT s.id,
           (SELECT i.id
            FROM specie_map sm
            JOIN image i ON sm.image_id = i.id
            WHERE sm.specie_id = s.id
            ORDER BY i.karma DESC
            LIMIT 1) as image_id
    FROM specie s
    ORDER BY s.commonname
    LIMIT 0, 24
) as ids
JOIN specie
  ON ids.id = specie.id
JOIN image as maximage
  ON maximage.id = ids.image_id
JOIN imagefile
  ON imagefile.image_id = ids.image_id AND imagefile.type = 'small';

You will need the following indexes:

您将需要以下索引：

• (commonname) on specie
• （普通名字）在物种上
• a composite (specie_id, image_id) on specie_map
• specie_map上的复合（specie_id，image_id）
• a composite (id, karma) on image
• 图像上的复合（id，karma）
• a composite (image_id, type) on imagefile
• imagefile上的复合（image_id，type）

Paging now should happen within the subquery.

现在分页应该在子查询中进行。

The idea is to make complex computations within a subquery that operates with ids only and join for the rest of the data at the top. The data would be ordered in the order of the results of the subquery.

我们的想法是在仅使用id操作的子查询中进行复杂计算，并在顶部连接其余数据。数据将按子查询结果的顺序排序。

#3

It would be better if you could provide the table structures and indexes. I came up with this alternative, it would be nice if you could try this and tell me what happens (I am curious!):

如果你能提供表结构和索引会更好。我提出了这个替代方案，如果你能尝试这个并且告诉我发生了什么会很好（我很好奇！）：

SELECT t.*, imf.* FROM (
  SELECT s.*, (SELECT id FROM image WHERE karma = MAX(i.karma) LIMIT 1) AS max_image_id 
  FROM image i 
  INNER JOIN specie_map smap ON smap.image_id = i.id
  INNER JOIN specie s ON s.id = smap.specie_id
  GROUP BY s.commonname 
  ORDER BY s.commonname ASC
  LIMIT 24
) t INNER JOIN imagefile imf
ON t.max_image_id = imf.image_id AND imf.type = 'small'