[ThorstenA]

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:

1. 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)
2. 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