Subquery or not subheading?
I'm starting to get familiar with subqueries, but ATM I'm just scratching my head as to why MySQL is kicking itself in the groin with the following:
SELECT
id_topic,
id_member_comment,
pd.username,
dt_post
FROM forum_comment c
LEFT JOIN persondata pd
ON c.id_member_comment = pd.id_member
WHERE id_comment IN (
SELECT MAX(last_id_comment) AS id_comment
FROM forum_topic
GROUP BY cat_id
);
If I run the query SELECT MAX(last_id_comment) AS id_comment FROM forum_topic GROUP BY cat_id
separately and replace the result set in the section id_comment IN (...)
, then it executes in an instant, but when the above query is executed, with a subquery, it takes age to complete.
The optimizer goes through all the comments (many millions) one by one instead of running the subquery first and using its values? What am I missing here?
+1
source to share
1 answer
Try to move IN to FROM clause as inline derived table
SELECT
id_topic, id_member_comment, pd.username, dt_post
FROM
(
SELECT MAX(last_id_comment) AS id_comment
FROM forum_topic
GROUP BY cat_id
) AS foo
JOIN
forum_comment c ON foo.id_comment = c.id_comment --AND a cat_id join too?
LEFT JOIN
persondata pd ON c.id_member_comment = pd.id_member;
+4
source to share