Have the MySQL SELECT query below that is awfully slow.
It takes ~1.0 seconds to execute despite have only 300K rows and indexes, so I would love to find a way to get it to execute faster since it’s a query that needs to be run again and again.
SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice) FROM products p JOIN shops s ON p.shopid = s.id JOIN products_category pc ON p.id = pc.product_id JOIN categories c ON pc.category_id = c.id WHERE brand_id > 0 AND pc.category_id = 46 AND pc.active = 1 AND p.price > 0 AND p.saleprice > 0 AND p.saleprice < p.price AND (last_seen > DATE_SUB(NOW(), INTERVAL 2 DAY)) GROUP BY p.image
The query returns 960 rows.
The table products has 300.000 rows and these columns:
id (int, primary key) name (varchar 512) image (varchar 512) price (int) saleprice (int) added (datetime) last_seen (datetime)
It has one index across multiple columns in this order:
brand_id (int), shopid (int), last_seen (datetime), price (int), saleprice (int)
The table products_categories also has 300.000 rows and these columns:
id (int, primary key) product_id (int) category_id (int) active (int)
It has two indexes across multiple columns:
category_id (int), active (int) product_id (int), active (int)
Based on similar questions here, I have tried nesting things with an inner select:
SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice) FROM (SELECT * FROM products WHERE brand_id > 0 AND price > 0 AND saleprice > 0 AND saleprice < price AND (last_seen > DATE_SUB(NOW(), INTERVAL 3 DAY))) p JOIN shops s ON p.shopid = s.id JOIN products_category pc ON p.id = pc.product_id JOIN categories c ON pc.category_id = c.id WHERE pc.category_id = 46 AND pc.active = 1 GROUP BY p.image
It didn’t help. The version with the inner select takes ~1,3 seconds to execute.
The problem seems to be the join between products and products_category, i.e. the two big tables with 300K rows each.
Maybe there’s a trick I can do with my indexes? Or can any of you spot something else I should optimize?
EXPLAIN of the query:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE c N const PRIMARY PRIMARY 4 const 1 100.00 Using temporary; Using filesort 1 SIMPLE pc N ref category_id etc,product_id etc category_id etc 10 const,const 43104 100.00 Using where 1 SIMPLE p N eq_ref PRIMARY,brand_id etc PRIMARY 4 pc.product_id 1 5.00 Using where 1 SIMPLE s N eq_ref PRIMARY PRIMARY 4 p.shopid 1 100.00 N