subquery breaks optimization
I've got 4.1.13 and a table there:
CREATE TABLE `prices` (
`id_product` int(11) NOT NULL default '0',
`id_shop` mediumint(9) NOT NULL default '0',
`seton` date NOT NULL default '0000-00-00',
`price` decimal(7,2) NOT NULL default '0.00',
PRIMARY KEY (`id_product`,`id_shop`,`seton`),
KEY `id_product` (`id_product`),
KEY `id_shop` (`id_shop`),
KEY `id_shop_2` (`id_shop`, `seton`),
KEY `id_product_2` (`id_product`,`price`),
KEY `seton` (`seton`),
KEY `price` (`price`)
) ENGINE=InnoDB
consider it a price list for a set of shops offering (optionally) intersecting set of products; every price change for every product is stored, zero price means a shop stops selling a product. I need to query a minimal price for a specific product (120) for a date month ago:
SELECT MIN(price) FROM prices WHERE (id_shop, seton) IN (SELECT pg.id_shop, MAX(pg.seton) FROM prices pg WHERE pg.id_product = 120 AND pg.seton <= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND pg.price <> 0 GROUP BY pg.id_shop);
if you run EXPLAIN you'll see 3 strange things:
1) inner query is marked as "DEPENDENT SUBQUERY" though it's not
2) no key is used to optimize WHERE in outer query at all
3) inner query key choise seems to be always PRIMARY regardless of no of price records for each product (when adding price and seton keys is wise)
Any ideas on how to break this behaviour?
Edited 2 time(s). Last edit at 01/20/2006 06:54AM by Kiryll Mirnenko.