1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

mysql 5.6.9-rc (root) [sakila]> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

mysql 5.6.9-rc (root) [sakila]> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: index
possible_keys: PRIMARY
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1000
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 2
        Extra: Using index; FirstMatch(film)
2 rows in set (0.00 sec)

mysql 5.6.9-rc (root) [sakila]> pager > /dev/null
PAGER set to '> /dev/null'
mysql 5.6.9-rc (root) [sakila]> SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)\G
997 rows in set (0.00 sec)

mysql 5.6.9-rc (root) [sakila]> nopager
PAGER set to stdout
mysql 5.6.9-rc (root) [sakila]> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 1001  |
| Handler_read_last          | 0     |
| Handler_read_next          | 1000  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.01 sec)

mysql 5.6.9-rc (root) [sakila]> set optimizer_switch='semijoin=off,firstmatch=off,materialization=off,loosescan=off';
Query OK, 0 rows affected (0.00 sec)

mysql 5.6.9-rc (root) [sakila]> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: film
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1000
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: film_actor
         type: index_subquery
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: func
         rows: 2
        Extra: Using index
2 rows in set (0.00 sec)

mysql 5.6.9-rc (root) [sakila]> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6.9-rc (root) [sakila]> pager > /dev/null
PAGER set to '> /dev/null'
mysql 5.6.9-rc (root) [sakila]> SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)\G
997 rows in set (0.01 sec)

mysql 5.6.9-rc (root) [sakila]> nopager
PAGER set to stdout
mysql 5.6.9-rc (root) [sakila]> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 1001  |
| Handler_read_last          | 0     |
| Handler_read_next          | 1000  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

mysql 5.6.9-rc (root) [sakila]> notee