使用JOIN和COUNT优化MySQL查询

Question

我有以下MySQL查询：

SELECT t1.id, t1.releaseid, t1.site, t1.date, t2.pos FROM `tracers` as t1
LEFT JOIN (
    SELECT `releaseid`, `date`, COUNT(*) AS `pos` 
    FROM `tracers` GROUP BY `releaseid`
) AS t2 ON t1.releaseid = t2.releaseid AND t2.date <= t1.date 
ORDER BY `date` DESC , `pos` DESC LIMIT 0 , 100

这个想法是选择一个版本，并计算有多少其他网站在记录日期之前也发布了它，以获得位置。

解释说：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY t1  ALL NULL    NULL    NULL    NULL    498422  Using temporary; Using filesort
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    91661    
2   DERIVED tracers index   NULL    releaseid   4   NULL    498422   

对如何避免使用临时文件排序有什么建议吗？这需要很长的时间。我想过并尝试过的索引没有任何帮助。

Answer 1

尝试分别在tracers.releaseid和tracers.date上添加索引

Answer 2

1. 确保您的连接上有索引，子查询必须在左连接的左侧。
2. 将ORDER BY和LIMIT子句放在子查询内。

Answer 3

尝试使用两个索引，一个在(date)上，另一个在(releaseid, date)上。

另一件事是，您的查询似乎没有执行您所描述的操作。它真的计算正确吗？

尝试将其重写为：

SELECT t1.id, t1.releaseid, t1.site, t1.`date`
     , COUNT(*) AS pos
FROM tracers AS t1
  JOIN tracers AS t2
    ON  t2.releaseid = t1.releaseid
    AND t2.`date` <= t1.`date` 
GROUP BY t1.releaseid
ORDER BY t1.`date` DESC
       , pos DESC
LIMIT 0 , 100

或如下所示：

SELECT t1.id, t1.releaseid, t1.site, t1.`date`
     , ( SELECT COUNT(*)
         FROM tracers AS t2
         WHERE t2.releaseid = t1.releaseid
           AND t2.`date` <= t1.`date`
       ) AS pos
FROM tracers AS t1
ORDER BY t1.`date` DESC
       , pos DESC
LIMIT 0 , 100