Description:
EXPLAIN FORMAT=JSON has different query_cost, rows_examined_per_scan, rows_produced_per_join, cost_info for the same table definition and data, depending when index was added. Output of SHOW STATUS LIKE 'Handler%' is same for both cases.
How to repeat:
CREATE TABLE `t1` (
`f1` varchar(20) NOT NULL,
`f2` varchar(100) NOT NULL DEFAULT '',
`f3` bigint(20) NOT NULL,
PRIMARY KEY (`f1`,`f3`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into t1 values(encrypt(uuid()), uuid(), (FLOOR( 1 + RAND( ) * 60000 )));
insert into t1 select encrypt(uuid()), uuid(), (FLOOR( 1 + RAND( ) * 60000 )) from t1;
--repeat 16 times
update t1 set f1 = f3%100;
explain format=json select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2;
EXPLAIN {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "180.83"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"table": {
"table_name": "aa",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"f1",
"f3"
],
"key_length": "70",
"rows_examined_per_scan": 635,
"rows_produced_per_join": 211,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "53.83",
"eval_cost": "42.33",
"prefix_cost": "180.83",
"data_read_per_join": "77K"
},
"used_columns": [
"f1",
"f2",
"f3"
],
"attached_condition": "((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000))"
}
}
}
}
Warnings:
Level Note
Code 1003
Message /* select#1 */ select `test`.`aa`.`f2` AS `f2`,max(`test`.`aa`.`f3`) AS `f3` from `test`.`t1` `aa` where ((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000)) group by `test`.`aa`.`f2`
-- Check specified values. Looks pretty effective, but in fact is not:
flush status;
select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2;
show status like 'Handler%';
Variable_name Value
Handler_commit 1
Handler_delete 0
Handler_discover 0
Handler_external_lock 2
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 636
Handler_read_last 0
Handler_read_next 635
Handler_read_prev 0
Handler_read_rnd 635
Handler_read_rnd_next 636
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 635
Now lets create dummy table, truncate original one, drop the key, insert rows first and add key back.
create table t2 like t1;
insert into t2 select * from t1;
alter table t1 drop primary key;
truncate table t1;
insert into t1 select * from t2;
alter table t1 add primary key(`f1`,`f3`,`f2`);
Now lets check how output of EXPLAIN FORMAT=JSON changed:
explain format=json select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2;
EXPLAIN {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "890.67"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "635.00"
},
"table": {
"table_name": "aa",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"f1",
"f3"
],
"key_length": "70",
"rows_examined_per_scan": 635,
"rows_produced_per_join": 635,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "128.67",
"eval_cost": "127.00",
"prefix_cost": "255.67",
"data_read_per_join": "233K"
},
"used_columns": [
"f1",
"f2",
"f3"
],
"attached_condition": "((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000))"
}
}
}
}
Warnings:
Level Note
Code 1003
Message /* select#1 */ select `test`.`aa`.`f2` AS `f2`,max(`test`.`aa`.`f3`) AS `f3` from `test`.`t1` `aa` where ((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000)) group by `test`.`aa`.`f2`
-- And test real execution path again.
flush status;
select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2;
show status like 'Handler%';
Variable_name Value
Handler_commit 1
Handler_delete 0
Handler_discover 0
Handler_external_lock 2
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 636
Handler_read_last 0
Handler_read_next 635
Handler_read_prev 0
Handler_read_rnd 635
Handler_read_rnd_next 636
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 635
Description: EXPLAIN FORMAT=JSON has different query_cost, rows_examined_per_scan, rows_produced_per_join, cost_info for the same table definition and data, depending when index was added. Output of SHOW STATUS LIKE 'Handler%' is same for both cases. How to repeat: CREATE TABLE `t1` ( `f1` varchar(20) NOT NULL, `f2` varchar(100) NOT NULL DEFAULT '', `f3` bigint(20) NOT NULL, PRIMARY KEY (`f1`,`f3`,`f2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into t1 values(encrypt(uuid()), uuid(), (FLOOR( 1 + RAND( ) * 60000 ))); insert into t1 select encrypt(uuid()), uuid(), (FLOOR( 1 + RAND( ) * 60000 )) from t1; --repeat 16 times update t1 set f1 = f3%100; explain format=json select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "180.83" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "table": { "table_name": "aa", "access_type": "range", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "f1", "f3" ], "key_length": "70", "rows_examined_per_scan": 635, "rows_produced_per_join": 211, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "53.83", "eval_cost": "42.33", "prefix_cost": "180.83", "data_read_per_join": "77K" }, "used_columns": [ "f1", "f2", "f3" ], "attached_condition": "((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000))" } } } } Warnings: Level Note Code 1003 Message /* select#1 */ select `test`.`aa`.`f2` AS `f2`,max(`test`.`aa`.`f3`) AS `f3` from `test`.`t1` `aa` where ((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000)) group by `test`.`aa`.`f2` -- Check specified values. Looks pretty effective, but in fact is not: flush status; select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2; show status like 'Handler%'; Variable_name Value Handler_commit 1 Handler_delete 0 Handler_discover 0 Handler_external_lock 2 Handler_mrr_init 0 Handler_prepare 0 Handler_read_first 0 Handler_read_key 636 Handler_read_last 0 Handler_read_next 635 Handler_read_prev 0 Handler_read_rnd 635 Handler_read_rnd_next 636 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 0 Handler_write 635 Now lets create dummy table, truncate original one, drop the key, insert rows first and add key back. create table t2 like t1; insert into t2 select * from t1; alter table t1 drop primary key; truncate table t1; insert into t1 select * from t2; alter table t1 add primary key(`f1`,`f3`,`f2`); Now lets check how output of EXPLAIN FORMAT=JSON changed: explain format=json select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2; EXPLAIN { "query_block": { "select_id": 1, "cost_info": { "query_cost": "890.67" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "635.00" }, "table": { "table_name": "aa", "access_type": "range", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "f1", "f3" ], "key_length": "70", "rows_examined_per_scan": 635, "rows_produced_per_join": 635, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "128.67", "eval_cost": "127.00", "prefix_cost": "255.67", "data_read_per_join": "233K" }, "used_columns": [ "f1", "f2", "f3" ], "attached_condition": "((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000))" } } } } Warnings: Level Note Code 1003 Message /* select#1 */ select `test`.`aa`.`f2` AS `f2`,max(`test`.`aa`.`f3`) AS `f3` from `test`.`t1` `aa` where ((`test`.`aa`.`f1` = '42') and (`test`.`aa`.`f3` <= 30000)) group by `test`.`aa`.`f2` -- And test real execution path again. flush status; select aa.f2 , max(aa.f3) as f3 from t1 aa where f1 = '42' and f3 <= 30000 group by aa.f2; show status like 'Handler%'; Variable_name Value Handler_commit 1 Handler_delete 0 Handler_discover 0 Handler_external_lock 2 Handler_mrr_init 0 Handler_prepare 0 Handler_read_first 0 Handler_read_key 636 Handler_read_last 0 Handler_read_next 635 Handler_read_prev 0 Handler_read_rnd 635 Handler_read_rnd_next 636 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 0 Handler_write 635