Re: avoid filesort

[irek kordirko]

Hi,

create the following index:

create index postdate_thid_ix on f_post( postdate, thread_id );

and rewrite your query to this one:

    select t.id, t.subject, x.postdate lastpost
    from f_thread t 
    JOIN (
        SELECT  distinct thread_id, postdate
        FROM  f_post
        order by postdate desc, thread_id desc limit 25
    ) x ON t.id = x.thread_id
    order by lastpost desc

Notice that this query is not 100% equivalent to your original query.
Generally it will give the same results except the case when f_post
does not contain posts for at least 25 unique thread_id - for example if f_post
is empty, this query returns empty resultset, while your original query returns
25 records with null postdate.
To avoid this case you may use this query:

SELECT id, subject, lastpost
FROM (
    select t.id, t.subject, x.postdate lastpost
    from f_thread t 
    JOIN (
        SELECT  distinct thread_id, postdate
        FROM  f_post
        order by postdate desc, thread_id desc limit 25
    ) x ON t.id = x.thread_id
    order by lastpost desc
) xx
UNION ALL
SELECT id, subject, lastpost
FROM (
   select id, subject, null lastpost
   FROM f_thread
   LIMIT 25
) yy
order by lastpost desc
;

Maybe these queries look ugly, but should perform much better (but only if you create the index on postdate + thread_id !!!).
Give them a try and you see how they perform on your data.
Regards



Edited 1 time(s). Last edit at 01/25/2012 01:25PM by irek kordirko.