对每项物品，找出最贵价格的物品的经销商，sql优化分析
-> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);使用shell脚本构造数据
#! /bin/bash
for ((i=10009;i explain SELECT article, dealer, price FROMshop s1 WHEREprice=(SELECT MAX(s2.price) FROM shop s2WHERE s1.article = s2.article);
+----+--------------------+-------+------+---------------+---------+---------+--------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+---------+---------+--------------+--------+-------------+
|1 | PRIMARY | s1 | ALL| NULL | NULL | NULL | NULL | 125935 | Using where |
|2 | DEPENDENT SUBQUERY | s2 | ref| PRIMARY | PRIMARY | 4 | b.s1.article | 1 | |
+----+--------------------+-------+------+---------------+---------+---------+--------------+--------+-------------+
2 rows in set (0.00 sec)
mysql> explain select article,(select s2.dealer from shop s2 where s2.price = max(s1.price) limit 1) from shop s1 group by article;
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type| possible_keys | key | key_len | ref| rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|1 | PRIMARY | s1 | index | NULL | PRIMARY | 24 | NULL | 125935 | |
|2 | DEPENDENT SUBQUERY | s2 | ALL | NULL | NULL | NULL | NULL | 125935 | Using where |
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
mysql> explain SELECT article, dealer, price FROM shop s1 WHEREpricein (select max(price) from shop group by article);
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type| possible_keys | key | key_len | ref| rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|1 | PRIMARY | s1 | ALL | NULL | NULL | NULL | NULL | 125935 | Using where |
|2 | DEPENDENT SUBQUERY | shop| index | NULL | PRIMARY | 24 | NULL | 1 | |
+----+--------------------+-------+-------+---------------+---------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
页:
[1]