EXPLAIN
SELECT a.* ,b.* from
(SELECT s1.CO,s1.C2 FROM tb1 s1 WHERE s1.C5 = 0) a
LEFT JOIN
(SELECT s1.CO,MIN(s1.C4) AS C1 FROM tb2 s1 WHERE s1.C3='生活服务' GROUP BY s1.CO) b
on a.CO= b.CO
tb1 和tb2 中CO都已经建立了索引,下面是explain的结果:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1166 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1183 | |
| 3 | DERIVED | s1 | ALL | NULL | NULL | NULL | NULL | 15174 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | s1 | ALL | NULL | NULL | NULL | NULL | 1322 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
问题应该出在select结果再left join没有用到索引吧,怎么优化下呢,谢谢各位指点一二。
SELECT a.* ,b.* from
(SELECT s1.CO,s1.C2 FROM tb1 s1 WHERE s1.C5 = 0) a
LEFT JOIN
(SELECT s1.CO,MIN(s1.C4) AS C1 FROM tb2 s1 WHERE s1.C3='生活服务' GROUP BY s1.CO) b
on a.CO= b.CO
tb1 和tb2 中CO都已经建立了索引,下面是explain的结果:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1166 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1183 | |
| 3 | DERIVED | s1 | ALL | NULL | NULL | NULL | NULL | 15174 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | s1 | ALL | NULL | NULL | NULL | NULL | 1322 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
问题应该出在select结果再left join没有用到索引吧,怎么优化下呢,谢谢各位指点一二。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货