CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c11` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `t2` (
`c2` int(11) DEFAULT NULL,
`c22` int(11) NOT NULL DEFAULT '1',
`c23` int(11) NOT NULL DEFAULT '1',
KEY `c2` (`c2`),
KEY `c22` (`c22`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> select * from t1;
+------+-----+
| c1 | c11 |
+------+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 16 | 1 |
| 17 | 1 |
| 18 | 1 |
| 111 | 1 |
+------+-----+
mysql> select * from t2;
+------+-----+-----+
| c2 | c22 | c23 |
+------+-----+-----+
| 2 | 1 | 1 |
| 4 | 1 | 1 |
| 5 | 1 | 1 |
| 6 | 1 | 1 |
| 7 | 1 | 1 |
| 8 | 1 | 1 |
| 11 | 1 | 1 |
| 2 | 1 | 1 |
+------+-----+-----+
Следующий подзапрос является зависимым:mysql> explain extended select c1 from t1 where (c1,c11) in (select c2,c22 from
t2)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t2
type: ALL
possible_keys: c2,c22
key: NULL
key_len: NULL
ref: NULL
rows: 8
filtered: 75.00
Extra: Using where
2 rows in set, 1 warning (0.09 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------
| Level | Code | Message|
+-------+------+----------------------------------------------------------------
| Note | 1003 | select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`c1`,`test`.`t1`.`c11`),<exists>(select `test`.`t2`.`c2`,`test`.`t2`.`c22` from `test`.`t2` where ((<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`) and (<cache>(`test`.`t1`.`c11`) = `test`.`t2`.`c22`)))) |
+-------+------+----------------------------------------------------------------
1 row in set (0.00 sec)
Следующий подзапрос является независимым:mysql> explain extended select c1 from t1 where (c1,c11) in (select c2,c22 from
t2 where c2>1 and c22<20)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t2
type: index_subquery
possible_keys: c2,c22
key: c2
key_len: 5
ref: func
rows: 4
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`c1`,`test`.`t1`.`c11`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`c1`) in t2 on c2 where ((`test`.`t2`.`c2` > 1) and (`test`.`t2`.`c22` < 20) and (<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c2`) and (<cache>(`test`.`t1`.`c11`) = `test`.`t2`.`c22`))))) |
+-------+------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Если сравнить планы отдельно подзапросов, то мы увидим расхождение в части possible_keys
mysql> explain select c2,c22 from t2 where c2>1 and c22<20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: c2,c22
key: NULL
key_len: NULL
ref: NULL
rows: 8
Extra: Using where
1 row in set (0.01 sec)
mysql> explain select c2,c22 from t2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
Extra:
1 row in set (0.00 sec)
P.S. что значит <index_lookup> в переписанном оптимизатором независимом подзапросе?
