MySQL Forums
Forum List  »  Performance

Re: Improving Slow Query
Posted by: Rick James
Date: May 04, 2010 11:52PM

Do EXPLAIN PARTITION ...; I suspect it is not doing any pruning. One way that might help with pruning:
1. Do your SELECT ... WHERE ... AND pub_date > 1268553600 ORDER BY...
2. If step 1 did not get enough rows, run the original query.

Another trick is to use the index to get the desired ids, then get the bulky stuff:
Current query:
select  id, title, link, description, author,
        image, guid, feature_id, feature_name,
        source, source_name, keyword, pub_date,
        feature_username, feature_url, featured,
        lat, lon
    from  item
    where  feature_id in (867,307,1779,2363,
       2377,199,2231,2607)
    order by  pub_date DESC
    limit  0,20
Revised (may help):
select  id, title, link, description, author,
        image, guid, feature_id, feature_name,
        source, source_name, keyword, pub_date,
        feature_username, feature_url, featured,
        lat, lon
    from  item i
    JOIN  ( SELECT id, pub_date, feature_id
               where  feature_id in (867,307,1779,2363, 2377,199,2231,2607)
               order by  pub_date DESC
               limit  0,20 
            FROM item 
          ) j
    ON j.id = i.id AND j.pub_date = i.pub_date AND j.feature_id = i.feature_id
    ORDER BY pub_date DESC

Another technique _might_ be to somehow figure out the pub_date range up front (perhaps with the subquery I suggested above), then add that to the main query. This is to try to get "partition pruning" to kick in.

Options: ReplyQuote


Subject
Views
Written By
Posted
3970
May 03, 2010 01:03AM
Re: Improving Slow Query
1297
May 04, 2010 11:52PM
1134
May 05, 2010 04:26AM
1304
May 05, 2010 09:13AM


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.