|
If the where condition of a query contains a non-correlated, non-expensive subquery then
then different executions of the prepared statement made for the query may use
different execution plans.
The following test case demonstrates this.
create table t1 (id int, c varchar(3), key idx(c))engine=myisam;
|
insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy');
|
|
prepare stmt1 from
|
"explain extended
|
select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
|
execute stmt1;
|
execute stmt1;
|
deallocate prepare stmt1;
|
|
prepare stmt2 from
|
"explain extended
|
select * from t1 where (1, 2) in ( select 3, 4 )";
|
execute stmt2;
|
execute stmt2;
|
deallocate prepare stmt2;
|
|
drop table t1;
|
Here for the first and the second executions of the prepared statement stmt1 we have:
MariaDB [test]> execute stmt1;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
|
| 1 | PRIMARY | t1 | ref | idx | idx | 6 | const | 1 | 100.00 | Using index condition |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
|
|
MariaDB [test]> execute stmt1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|
| 1 | PRIMARY | t1 | ALL | idx | NULL | NULL | NULL | 4 | 100.00 | Using where |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|
While for the first and the second executions of the prepared statement stmt2 the plans are
as follows:
ariaDB [test]> execute stmt2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
|
MariaDB [test]> execute stmt2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | |
|
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|
|