|
create table ten(a int primary key);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table one_k(a int primary key);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
create table t10 (a int, b int, c int, key(a), key(b));
|
insert into t10 select A.a, A.a, A.a from one_k A, ten B;
|
set optimizer_trace=1;
|
explain select * from ten,t10 where t10.a=ten.a and t10.b=ten.a;
|
+------+-------------+-------+-------+---------------+---------+---------+------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------------+------+-------------+
|
| 1 | SIMPLE | ten | index | PRIMARY | PRIMARY | 4 | NULL | 10 | Using index |
|
| 1 | SIMPLE | t10 | ref | a,b | a | 5 | test.ten.a | 1 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+------------+------+-------------+
|
select * from information_schema.optimizer_trace\G
|
"plan_prefix": ["ten"],
|
"table": "t10",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "ref",
|
"index": "a",
|
"used_range_estimates": false,
|
"cause": "not available",
|
"rows": 1,
|
"cost": 20.00134269,
|
"chosen": true
|
},
|
{
|
"access_type": "ref",
|
"index": "b",
|
"used_range_estimates": false,
|
"cause": "not available",
|
"rows": 1,
|
"cost": 20.00134269,
|
"chosen": false,
|
"cause": "cost"
|
},
|
{
|
"type": "scan",
|
"chosen": false,
|
"cause": "cost"
|
}
|
],
|
"chosen_access_method": {
|
"type": "ref",
|
"records": 1,
|
"cost": 20.00134269,
|
"uses_join_buffering": false
|
}
|
},
|
Now, ask a question - which index was chosen? chosen_access_method doesn't show it.
If one looks carefully at the considered options, they have "chosen": false|true, but this is not obvious at the first glance.
|