关于Mysql left join索引的问题
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没有用到索引吧，怎么优化下呢，谢谢各位指点一二。
[解决办法]
很显然你这过滤条件是C5 和C3 在这两列上建立索引
[解决办法]
你的LEFT JOIN两端都已经是生成表了，而生成表哪来的索引呢？
[解决办法]
表的连接查询的两端都是新的表，新生的表是没有索引的。
