Details
Major Description
The test is
--source include/have_innodb.inc
|
create table t1(a int, key(a))engine=innodb;
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table t2(
|
id int primary key,
|
key1 int,
|
col1 int,
|
key(key1))engine=innodb;
|
insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456
|
from t1 A, t1 B, t1 C, t1 D;
|
|
|
alter table t2 add key2 int;
|
update t2 set key2=key1;
|
alter table t2 add key(key2);
|
analyze table t2;
|
flush tables;
|
analyze table t2;
|
flush tables;
|
|
|
analyze format=json select
|
(SELECT
|
concat(id, '-', key1, '-', col1)
|
FROM t2
|
WHERE t2.key1 = t1.a
|
ORDER BY t2.key2 ASC limit 1)
|
from
|
t1;
|
The output for analyze format=json is
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 852.7,
|
"table": {
|
"table_name": "t1",
|
"access_type": "index",
|
"key": "a",
|
"key_length": "5",
|
"used_key_parts": ["a"],
|
"r_loops": 1,
|
"rows": 10,
|
"r_rows": 10,
|
"r_total_time_ms": 2.819,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"subqueries": [
|
{
|
"expression_cache": {
|
"r_loops": 10,
|
"r_hit_ratio": 0,
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 10,
|
"r_total_time_ms": 848.11,
|
"outer_ref_condition": "t1.a is not null",
|
"read_sorted_file": {
|
"r_rows": 1,
|
"filesort": {
|
"sort_key": "t2.key2",
|
"r_loops": 10,
|
"r_total_time_ms": 844.61,
|
"r_limit": 0,
|
"r_used_priority_queue": true,
|
"r_output_rows": 2,
|
"table": {
|
"table_name": "t2",
|
"access_type": "ref",
|
"possible_keys": ["key1"],
|
"key": "key1",
|
"key_length": "5",
|
"used_key_parts": ["key1"],
|
"ref": ["test.t1.a"],
|
"r_loops": 10,
|
"rows": 48,
|
"r_rows": 100,
|
"r_total_time_ms": 831.09,
|
"filtered": 100,
|
"r_filtered": 1,
|
"attached_condition": ""
|
}
|
}
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
So main concern is that r_limit=0 in filesort, inside the subquery block.
The r_loops=10 for filesort block shows that we execute the subquery 10 times and perform filesort each time but somehow the value for r_limit is not set correctly.