ThorstenA
03-13-2010, 07:55 AM
This query displays the 5 most posting users in the forum 1234 and its subforums between last year and now (+/- 5 minutes). I hoped with this time frame to cache the query within MySQL. I have two questions:
Why is this query not cached as it has to bring any time the same results? (Of course I removed SQL_NO_CACHE in productive environment)
How could I improve this query? The ORDER statement costs so much time, how could I rewrite the query to do the same?
Thanks so much!
SELECT SQL_NO_CACHE user.userid, user.username, count(*) AS post_count
FROM user
INNER JOIN post
ON post.userid = user.userid
AND post.dateline > 1000000
AND post.dateline < 20000000
INNER JOIN thread
ON thread.threadid = post.threadid
AND thread.forumid IN
(SELECT 1234 UNION
SELECT forumid
FROM forum
WHERE parentlist LIKE "%,1234,%"
)
GROUP BY user.username
ORDER BY post_count DESC
LIMIT 5
---------------------------
Found the answer for question two. I must rewrite the IN statement with a FROM subquery. FROM subqueries are processed very fast within Mysql. This change - INNER JOINS seem not to have a measurable effect on speed - makes the query run 30 times faster.
Regarding question one. It will not be cached if I set a post timeframe (x < post_date < y), though. Someone has an idea why is that?
SELECT user.userid, user.username, count( * ) AS postcount
FROM user, post, thread,
(SELECT 1234 AS forumid UNION
SELECT forumid
FROM forum
WHERE parentlist LIKE "%,1234,%"
) AS tmp_a
WHERE thread.forumid = tmp_a.forumid
AND user.userid = post.userid
AND post.threadid = thread.threadid
AND post.dateline >1000000
GROUP BY user.username
ORDER BY postcount DESC
LIMIT 5
Why is this query not cached as it has to bring any time the same results? (Of course I removed SQL_NO_CACHE in productive environment)
How could I improve this query? The ORDER statement costs so much time, how could I rewrite the query to do the same?
Thanks so much!
SELECT SQL_NO_CACHE user.userid, user.username, count(*) AS post_count
FROM user
INNER JOIN post
ON post.userid = user.userid
AND post.dateline > 1000000
AND post.dateline < 20000000
INNER JOIN thread
ON thread.threadid = post.threadid
AND thread.forumid IN
(SELECT 1234 UNION
SELECT forumid
FROM forum
WHERE parentlist LIKE "%,1234,%"
)
GROUP BY user.username
ORDER BY post_count DESC
LIMIT 5
---------------------------
Found the answer for question two. I must rewrite the IN statement with a FROM subquery. FROM subqueries are processed very fast within Mysql. This change - INNER JOINS seem not to have a measurable effect on speed - makes the query run 30 times faster.
Regarding question one. It will not be cached if I set a post timeframe (x < post_date < y), though. Someone has an idea why is that?
SELECT user.userid, user.username, count( * ) AS postcount
FROM user, post, thread,
(SELECT 1234 AS forumid UNION
SELECT forumid
FROM forum
WHERE parentlist LIKE "%,1234,%"
) AS tmp_a
WHERE thread.forumid = tmp_a.forumid
AND user.userid = post.userid
AND post.threadid = thread.threadid
AND post.dateline >1000000
GROUP BY user.username
ORDER BY postcount DESC
LIMIT 5