题: 查询有商品的栏目.
按上面的理解,我们用join来操作,如下:
mysql> select c.cat_id,cat_name from ecs_category as c inner join goods as g on c.cat_id=g.cat_id group by cat_name;
优化1: 在group时, 用带有索引的列来group, 速度会稍快一些,另外, 用int型 比 char型 分组,也要快一些.
优化2: 在group时, 我们假设只取了A表的内容,group by 的列,尽量用A表的列, 会比B表的列要快.
优化3: 从语义上去优化：select cat_id,cat_name from ecs_category where exists(select * from goods where goods.cat_id=ecs_category.cat_id)
| 36 | 0.00039075 | select c.cat_id,cat_name from ecs_category as c inner join goods as g on c.cat_id=g.cat_id group by cat_name |
| 37 | 0.00038675 | select c.cat_id,cat_name from ecs_category as c inner join goods as g on c.cat_id=g.cat_id group by cat_id |
| 38 | 0.00035650 | select c.cat_id,cat_name from ecs_category as c inner join goods as g on c.cat_id=g.cat_id group by c.cat_id |
| 40 | 0.00033500 | select cat_id,cat_name from ecs_category where exists (select * from goods where goods.cat_id=ecs_category.cat_id)
from 型子查询:
注意::内层from语句查到的临时表, 是没有索引的，所以: from的返回内容要尽量少.
