MySQL correlated subquery SUM() ORDER BY
Is there anyway to optimize the following query:
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
Us开发者_JAVA百科ing ORDER BY col1_count DESC
takes a long time.
Thanks.
Just make a normal join with your comparison in the join's on
clause:
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
It should be way faster this way - it's basically one query instead of "n" queries, although it won't get any help from indexes using the LIKE
operator with a leading %
精彩评论