MySQL Forums
Forum List  »  Optimizer & Parser

Re: avoid filesort
Posted by: irek kordirko
Date: January 25, 2012 07:24PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
1597
January 22, 2012 01:08PM
971
January 24, 2012 01:17AM
1064
January 24, 2012 04:36PM
Re: avoid filesort
1164
January 25, 2012 07:24PM
1009
January 26, 2012 04:57AM
1001
January 25, 2012 08:26PM
977
January 26, 2012 04:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.