我有下表
CREATE TABLE `prod_prices` ( `id` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `date` date NOT NULL, `price` decimal(10,2) NOT NULL, PRIMARY KEY (`id`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
还有一些样本数据
INSERT INTO `prod_prices` (`id`, `date`, `price`) VALUES ('plan_a', '2012-06-15', 10.20), ('plan_a', '2012-06-16', 10.30), ('plan_b', '2012-06-15', 5.20), ('plan_b', '2012-06-16', 5.30), ('plan_b', '2012-06-17', 5.50);
并想知道每个计划的最新价格。我有以下查询
SELECT p1.id, p1.date, p1.price FROM prod_prices p1 LEFT JOIN prod_prices p2 ON p1.id = p2.id AND p1.date < p2.date WHERE p2.id IS NULL
尽管可以产生真正的糟糕表现,但这种方法还是有效的。 EXPLAIN表演
EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p1 ALL NULL NULL NULL NULL 5 1 SIMPLE p2 ref PRIMARY PRIMARY 98 p1.id 1 Using where; Using index; Not exists
因此p1表选择不使用任何索引。什么事?
p1
这应该给出相同的结果,并且性能要好得多。
SELECT p1.* FROM prod_prices p1 INNER JOIN ( SELECT ID, MAX(Date) AS Date FROM prod_prices GROUP BY ID ) AS p2 ON p1.ID = p2.ID AND p1.Date = p2.Date