mysql语句优化问题

Question

三表查询SELECT a.Id AS aId,a.Sn,a.Click,a.ShowMode,a.Name,a.MarketPrice,a.VipPrice,a.ActualNum,c.ChinaName AS cChinaName,d.Pic80,d.PicBig FROM PRODUCT AS a LEFT JOIN Cate AS c ON a.SubCateId=c.Id LEFT JOIN ProductPic AS d ON d.DefaultPic=1 AND a.Id=d.ProId ORDER BY a.Id DESC;PRODUCT 里的id和ProductPic里的proId对应，一个proId又分DefaultPic=1(产品主图)0(副图)PRODUCT 里的subcateid对应CATE里的Id(产品类别表);PRODUCT 里现有3312个产品，而 SELECT ProId FROM ProductPic WHERE DefaultPic=1 GROUP BY ProId 的结果也是3312
我想问能不能优化最上面的SQL，避免用外联，查询速度好慢，
而我优化后的SQL语句SELECT a.Id AS aId,a.Sn,a.Click,a.ShowMode,a.Name,a.MarketPrice,a.VipPrice,a.ActualNum,c.ChinaName AS cChinaName,d.Pic80,d.PicBig FROM PRODUCT AS a,CATE AS c,PRODUCTPIC AS d WHERE a.Id=d.ProId AND a.SubCateId=c.Id GROUP BY d.ProId ORDER BY a.Id DESC;  
结果却是3264;
求高手帮帮我，在线等

Answer 1

show index from xxx ;贴出来看一下各表的索引。explain 
SELECT a.Id AS aId,a.Sn,a.Click,a.ShowMode,a.Name,a.MarketPrice,a.VipPrice,a.ActualNum,c.ChinaName AS cChinaName,d.Pic80,d.PicBig 
FROM PRODUCT AS a LEFT JOIN Cate AS c ON a.SubCateId=c.Id 
LEFT JOIN ProductPic AS d ON d.DefaultPic=1 AND a.Id=d.ProId 
ORDER BY a.Id DESC;贴一下执行计划。

Answer 2

就3个表各1个主键索引，没了，执行计划 就是列出a.Id AS aId,a.Sn,a.Click,a.ShowMode,a.Name,a.MarketPrice,a.VipPrice,a.ActualNum,c.ChinaName AS cChinaName,d.Pic80,d.PicBig
，避免用左外联

Answer 3

show index from xxxexplain 
SELECT a ...

Answer 4

index :PRODUCT : Id
ProductPic : Id
Cate : Idexplain实在不明白你要干什么
我只要那句子避免左外联
中文：
id 定单号 点击数 显示模式 产品名称 市场价 会员价 库存数量 类别 图片1 图片2

Answer 5

1. left join 并不应该慢。
2. 优化主要是要看你的索引和数据分布。 优化后的结果一般会是 
  a) 创建索引
  b) 修改语句，调整条件
  c) 加入强制索引选择你不贴出相关信息，如何进行分析呢？ mysql> explain select * from t1 left join t2 on t1.id=t2.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows   | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
|  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL       | 499999 |       |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |      1 |       |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
2 rows in set (0.00 sec)mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |      499999 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)mysql>

Answer 6

mysql> explain SELECT a.Id AS aId,a.Sn,a.Click,a.ShowMode,a.Name,a.MarketPrice,a
.VipPrice,a.ActualNum,c.ChinaName AS cChinaName,d.Pic80,d.PicBig FROM PRODUCT AS
 a LEFT JOIN Cate AS c ON a.SubCateId=c.Id LEFT JOIN ProductPic AS d ON d.Defaul
tPic=1 AND a.Id=d.ProId ORDER BY a.Id DESC;+----+-------------+-------+--------+---------------+---------+---------+-------
-------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref
                   | rows  | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+-------
-------------------+-------+---------------------------------+
|  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL
                   | 16141 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | aigou0
011016.a.SubCateId |     1 |                                 |
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL
                   |  7687 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+-------
-------------------+-------+---------------------------------+

Answer 7

|  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL                     | 16141 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | aigou0011016.a.SubCateId |     1 |                 | 
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL                     |  7687 |                 |    这一步有些问题。 由于你不肯贴出你的索引情况。 也猜不出哪个是这个ProductPic表的主键只能猜着笼统说， 需要添加一个（ProId，DefaultPic）　或者其它的索引。

Answer 8

mysql> show index from cate;
+-------+------------+----------+--------------+-------------+-----------+-----
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Card
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-----
-------+----------+--------+------+------------+---------+
| cate  |          0 | PRIMARY  |            1 | Id          | A         |
    46 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-----
-------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> show index from productpic;
+-------+------------+----------+--------------+-------------+-----------+-----
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Card
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-----
-------+----------+--------+------+------------+---------+
| productpic  |          0 | PRIMARY  |            1 | Id          | A         |
    6789 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-----
-------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)mysql> show index from product;
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
dinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| product |          0 | PRIMARY  |            1 | Id          | A         |
   14567 |     NULL | NULL   |      | BTREE      |         |
| product |          0 | pId      |            1 | Id          | A         |
    7283 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+

Answer 9

唉，让你重贴都这么难！ 你创建两个索引算了。
create index xxxx on ProductPic(DefaultPic,ProId);
create index xxxx on ProductPic(ProId,DefaultPic);