Slow Queries

[ndahiya]

looking through my slow query log, i found a large number of these queries


SELECT
COUNT(*) AS count_posts
FROM post AS p
INNER JOIN thread as t ON p.threadid=t.threadid
WHERE p.visible=1
AND p.userid=11586;


from the looks of it, the only reason to check the thread table is to exclude orphan posts. Wonder if it is worth the performance penalty.

Another problematic query is:

SELECT
P.userid, COUNT(userid) AS times
FROM thread AS T
INNER JOIN post AS P ON T.lastpostid=P.postid
WHERE P.userid=11586
GROUP BY P.userid
ORDER BY P.dateline DESC, P.userid;

This can be easily fixed by adding an index on lastpostid on thread table. rows examined drops by a factor of 100.

Thoughts... ?

ndahiya