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