如何优化这个MySQL3表连接查询？

Question

SELECT 
  a.idmotcle,
  a.motcle, 
  count(DISTINCT c.id) as 'Programs',
  count(DISTINCT d.id) as 'Searches',
  FORMAT(count(DISTINCT d.id)/count(DISTINCT c.id),2) as 'S/P'

FROM motcle a
INNER JOIN motcle b 
     ON b.idmotcle=a.idmotcle AND a.archive=0
LEFT JOIN masters_keywords_nton c 
     ON c.id_motcle=a.idmotcle
LEFT JOIN master_search_log_tbl d 
     ON d.search_string LIKE concat('%',a.motcle,'%')
GROUP BY a.idmotcle
ORDER BY a.motcle

表-记录总数约为

摩托车- 200

masters_keywrods_nton - 1300

master_search_log_tbl - 4800

我已经对on子句中使用的所有字段建立了索引。

当我在生产环境中运行该查询时，该查询当前需要62.887秒。

我觉得有更好的方法来做连接和计数？

Answer 1

如果需要查询优化方面的帮助，请提供查询的解释计划和CREATE table语句。

了解查询的作用也很重要--您应该为查询中的每个短语/表达式提供一个解释。

比如，你为什么要在同一个领域加入motcle？为什么要使用左连接？为什么要将空值算作匹配项？

粗略估计，我怀疑这可能会满足您的需求：

SELECT 
  a.idmotcle,
  a.motcle, 
  count(DISTINCT c.id) as 'Programs',
  count(DISTINCT d.id) as 'Searches',
  FORMAT(count(DISTINCT d.id)/count(DISTINCT c.id),2) as 'S/P'
FROM motcle a
INNER JOIN masters_keywords_nton c 
 ON c.id_motcle=a.idmotcle
INNER JOIN master_search_log_tbl d 
 ON d.search_string LIKE concat('%',a.motcle,'%')
WHERE a.archive=0
GROUP BY a.idmotcle
ORDER BY a.motcle

但这里最明显的问题是你的数据没有正规化(像concat('%'，a.motcle，‘%’)这样的d.search_string)--修复这个问题会给你带来最大的性能提升，并修复代码中潜在的功能bug。(使用全文索引可能会提供暂时的缓解)。