I feel like I have optimized this query to the best of my ability, but it is still slow (usually takes 0.5s - 2s in development). It seems like the ORDER BY is the culprit, as when I get rid of it, it takes less than 20ms.

  inner join product_section on product_section.product_id = products.id
  and product_section.section_id in (1)
  inner join category_product on category_product.product_id = products.id
  and category_product.category_id = 83
  inner join images on images.product_id = products.id
  and images.is_featured = 1
  products.niche_id = 1
  and products.advertiser_main_site_epc >= 1
order by
  products.advertiser_status desc,
  products.instock desc,
  products.random_number asc,
  products.advertiser_main_site_epc desc,
  products.id desc

The EXPLAIN output says it's using filesort.

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE images index product_id product_id 7 111062 10.00 Using where; Using index; Using temporary; Using filesort
1 SIMPLE category_product eq_ref PRIMARY,product_id,category_product_category_id_idx PRIMARY 8 const,dfsystem.images.product_id 1 100.00 Using index
1 SIMPLE products eq_ref PRIMARY,idx_products_niche_id_advertiser_main_site_epc PRIMARY 4 dfsystem.images.product_id 1 50.00 Using where
1 SIMPLE product_section eq_ref PRIMARY,section_id PRIMARY 8 dfsystem.images.product_id,const 1 100.00 Using index

I have added an index on the 'products' table in an attempt to speed up the ORDER BY:

KEY idx_products_niche_id_advertiser_main_site_epc (niche_id,advertiser_main_site_epc DESC,advertiser_status DESC,instock DESC,random_number,id DESC)

However, it doesn't seem to be chosen by the optimizer.

Any suggestions?

  • Please provide SHOW CREATE TABLE for the tables. It seems that you have 4 tables, each with PRIMARY KEY(prodicut_id) instead of having a single table.
    – Rick James
    Commented Mar 29, 2023 at 23:56

2 Answers 2


Your index will not be able to avoid the ordering operation. The order of columns are incorrect. Here are the correct ones by the PawSQL tool we build,

CREATE INDEX PAW_IDX2074096603 ON products(niche_id,advertiser_status DESC,instock DESC,random_number,advertiser_main_site_epc DESC, id DESC);
CREATE INDEX PAW_IDX0475371273 ON images(is_featured,product_id);
CREATE INDEX PAW_IDX1365940357 ON category_product(category_id,product_id);
CREATE INDEX PAW_IDX1720780105 ON product_section(section_id,product_id);

This is probably the only useful index for products:

INDEX(niche_id, advertiser_main_site_epc) -- in this order

That handles the WHERE. Since it ends with a range test, it can't continue into doing the ORDER.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.