I have a question about MySQL(version 8.0.32) query_cost and actual execution time. The query_cost result shows that a larger query_cost may have a shorter actual execution time. Therefore, is there no necessary connection between query_cost and actual execution? So, what should we use to quantify SQL optimization, execution time? I understand that execution time is the result, and there are many factors that can affect it, such as network, machine performance, etc., which may not be related to the SQL itself.
In the following example, I suspect that the difference in the use of clustered indexes may be the reason. SQL1 scans to the last level of the B+ tree every time, while SQL2 only needs the ID, so it does not read the last level and uses less IO or memory. Is this correct? If so, why does the query_cost not reflect this?
I would appreciate it if you could recommend any relevant documents or blogs.
A table with 1000000 rows data. Here is the DDL:
CREATE TABLE `testdo` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`value` int NOT NULL,
`version` int NOT NULL ,
`created_at` datetime DEFAULT NULL,
`created_by` varchar(50) DEFAULT NULL,
`is_deleted` bit(1) DEFAULT NULL,
`modified_at` datetime DEFAULT NULL,
`modified_by` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
I have two queries and some extra info:
SQL 1(time cost:465ms query_cost:100873.90)
select * from testdo order by id limit 810000,30;
execute explain command:
explain format=json select * from testdo order by id limit 810000,30;
result
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "100873.90"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "testdo",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"rows_examined_per_scan": 810030,
"rows_produced_per_join": 994719,
"filtered": "100.00",
"cost_info": {
"read_cost": "1402.00",
"eval_cost": "99471.90",
"prefix_cost": "100873.90",
"data_read_per_join": "599M"
},
"used_columns": [
"id",
"name",
"value",
"version",
"created_at",
"created_by",
"is_deleted",
"modified_at",
"modified_by"
]
}
}
}
}
explain ANALYZE
explain ANALYZE select * from testdo order by id limit 810000,30;
result:
-> Limit/Offset: 30/810000 row(s) (cost=69914.55 rows=30) (actual time=465.053..465.098 rows=30 loops=1)
-> Index scan on testdo using PRIMARY (cost=69914.55 rows=810030) (actual time=0.082..444.687 rows=810030 loops=1)
SQL2(time cost:187ms query_cost:471480.20)
select t.* from (select id from testdo limit 810000,30)a,testdo t where a.id = t.id;
execute explain command:
explain format=json select t.* from (select id from testdo limit 810000,30)a,testdo t where a.id = t.id;
result:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "471480.20"
},
"nested_loop": [
{
"table": {
"table_name": "a",
"access_type": "ALL",
"rows_examined_per_scan": 810030,
"rows_produced_per_join": 810030,
"filtered": "100.00",
"cost_info": {
"read_cost": "10127.88",
"eval_cost": "81003.00",
"prefix_cost": "91130.88",
"data_read_per_join": "12M"
},
"used_columns": [
"id"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "98323.63"
},
"table": {
"table_name": "testdo",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"rows_examined_per_scan": 962512,
"rows_produced_per_join": 962512,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "2072.43",
"eval_cost": "96251.20",
"prefix_cost": "98323.63",
"data_read_per_join": "580M"
},
"used_columns": [
"id"
]
}
}
}
}
},
{
"table": {
"table_name": "t",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "8",
"ref": [
"a.id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 810030,
"filtered": "100.00",
"cost_info": {
"read_cost": "299346.33",
"eval_cost": "81003.00",
"prefix_cost": "471480.20",
"data_read_per_join": "488M"
},
"used_columns": [
"id",
"name",
"value",
"version",
"created_at",
"created_by",
"is_deleted",
"modified_at",
"modified_by"
]
}
}
]
}
}
explain ANALYZE
explain ANALYZE select t.* from (select id from testdo limit 810000,30)a,testdo t where a.id = t.id;
result:
-> Limit: 200 row(s) (cost=397678.84 rows=30) (actual time=187.429..187.622 rows=30 loops=1)
-> Nested loop inner join (cost=397678.84 rows=30) (actual time=187.428..187.621 rows=30 loops=1)
-> Table scan on a (cost=98326.73..98329.51 rows=30) (actual time=187.410..187.413 rows=30 loops=1)
-> Materialize (cost=98326.63..98326.63 rows=30) (actual time=187.409..187.409 rows=30 loops=1)
-> Limit/Offset: 30/810000 row(s) (cost=98323.63 rows=30) (actual time=187.376..187.392 rows=30 loops=1)
-> Covering index scan on testdo using PRIMARY (cost=98323.63 rows=962512) (actual time=0.034..167.223 rows=810030 loops=1)
-> Single-row index lookup on t using PRIMARY (id=a.id) (cost=0.37 rows=1) (actual time=0.007..0.007 rows=1 loops=30)
query_cost should be able to reflect the SQL execution situation to some extent.
The "cost model" fails to take into account various things, such as network delay, SSD vs HDD, caching, randomness of key lookups, table size, etc. Still, it is better than nothing. And it can be useful when comparing different index usage, etc.
order by id limit 810000,30;
will walk through the index on id (which is clustered with the data), skip over 810000 rows (costly) and then deliver 30 rows. If you knew the id at row 810000, it would be a huge amount faster to have
where id > ... order by id limit 30
Notice that "used_columns" includes all the columns. This is because of using the 'clustered' index. And "rows=810030", which is 81000+30.
(I don't know where "Limit: 200 row(s)" comes from.)
I do believe that LIMIT values are not adequately handled in EXPLAIN. Suggest you file a bug report relating to this. bugs.mysql.com
It seems like the Optimizer should realize that only 30 rows are created by the derived table, and fee that up to the outer query.