Re: How to get rid of 'filesort'?

[Rick James]

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.