MySQL Forums
Forum List  »  Optimizer & Parser

subquery breaks optimization
Posted by: Kiryll Mirnenko
Date: January 20, 2006 06:51AM

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

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.

Options: ReplyQuote

Written By
subquery breaks optimization
January 20, 2006 06:51AM
January 24, 2006 07:14PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.