MySQL Forums
Forum List  »  Optimizer & Parser

Re: How to get rid of 'filesort'?
Posted by: Rick James
Date: March 02, 2010 04:56PM

p has big rows. Let's give this a try:
ALTER TABLE s3_shop_product
  ADD INDEX(shop_id, status, product_id);
select  product_id /*....*/
  FROM s3_shop_product p2
  JOIN (
    SELECT p.product_id
      from  s3_shop_product p
      join  s3_folder_item as fi on fi.item_id=p.product_id
      join  s3_folder f on fi.folder_id=f.folder_id
      where  p.shop_id=6
        and  p.status=1
        and  f.tree_id=30
        and  f._left>=4
        and  f._right<=11
      group by  p.product_id
      order by  p.product_id
      limit  0, 5
  ) j  ON j.product_id = p2.product_id
  ORDER BY product_id
If /*...*/ has things other than p in it, then it gets messier.

Because of the new index, it will probably be "Using index". The outer query may still be "filesort", but don't worry, it is only 5 rows.

Options: ReplyQuote

Written By
February 28, 2010 08:51AM
March 01, 2010 04:51PM
March 01, 2010 11:27PM
Re: How to get rid of 'filesort'?
March 02, 2010 04:56PM

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.