Indexing for multiple ORDER BY in MySQL 8

Question

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.

select
  products.*
from
  products
  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
where
  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
limit
  90

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?

Answer 1

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);