MySQL 关联子查询 SUM() ORDER BY

Question

有没有办法优化下面的查询:

SELECT
  t1.id,
  (SELECT SUM(col1) FROM table_name_two t2 WHERE t2.name LIKE CONCAT('%',t1.name)) AS col1_count
FROM
  table_name_one t1
ORDER BY 
  col1_count DESC

使用 ORDER BY col1_count DESC 需要很长时间。

谢谢。

Answer 1

只需在连接的 on 子句中进行比较即可:

SELECT
  t1.id,
  SUM(t2.col1) AS col1_count
FROM table_name_one t1
LEFT JOIN table_name_two t2 on t2.name LIKE CONCAT('%', t1.name)
GROUP BY 1
ORDER BY 2 DESC

这种方式应该方式更快 - 它基本上是一个查询而不是“n”个查询，尽管它不会从使用 LIKE 运算符的索引中获得任何帮助以 %

开头