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.
The query:
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
