mysql> explain extended select * from (select * from t1 where id1 in (select id3 from t3 where id3=5)) t;
-> ;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | t3 | const | id3 | id3 | 5 | const | 1 | 100.00 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (49.53 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '5' AS `id1`,'5' AS `a1`,NULL AS `b1` from dual |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
"标题三"中和"标题一"中的SQL差别在于嵌套的子查询的形式是等号还是IN。
而IN的形式,被MySQL识别优化了标识子查询未“DERIVED ”了,这是MySQL从形式上目前只支持IN形式的子查询优化的优化,对于等号这样的形式,不支持。但是,从等式性质上,最终对"标题一"中的SQL进行了优化。
--------------------------------------分割线 --------------------------------------
Ubuntu 14.04下安装MySQL
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
--------------------------------------分割线 --------------------------------------