Bug #116710 | explain shows wrong cost value | ||
---|---|---|---|
Submitted: | 19 Nov 2024 12:47 | Modified: | 20 Nov 2024 2:55 |
Reporter: | Woson Wong | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | cost, EXPLAIN FORMAT=JSON, Optimizer |
[19 Nov 2024 12:47]
Woson Wong
[19 Nov 2024 12:48]
Woson Wong
OPTIMIZER_TRACE result
Attachment: OPTIMIZER_TRACE.txt (text/plain), 8.29 KiB.
[19 Nov 2024 14:36]
MySQL Verification Team
Hi Mr. Wong, Thank you for your bug report. This is truly a bug , but only a bug with the EXPLAIN in JSON format. Other forms of EXPLAIN work just fine: EXPLAIN -> Limit: 1 row(s) (cost=0.0421 rows=0.718)\n -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0421 rows=0.718)\n -> Index scan on test_ddl using idx_gmt_create (cost=0.0421 rows=2)\n EXPLAIN -> Limit: 1 row(s) (cost=0.0613 rows=0.1)\n -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0613 rows=0.1)\n -> Index scan on test_ddl using idx_gmt_create (cost=0.0613 rows=1)\n EXPLAIN -> Limit: 1 row(s) (cost=0.0421 rows=0.718) (actual time=7.16..7.16 rows=1 loops=1)\n -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0421 rows=0.718) (actual time=0.929..0.929 rows=1 loops=1)\n -> Index scan on test_ddl using idx_gmt_create (cost=0.0421 rows=2) (actual time=0.033..0.034 rows=2 loops=1)\n EXPLAIN -> Limit: 1 row(s) (cost=0.0613 rows=0.1) (actual time=0.0202..0.0204 rows=1 loops=1)\n -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0613 rows=0.1) (actual time=0.0196..0.0196 rows=1 loops=1)\n -> Index scan on test_ddl using idx_gmt_create (cost=0.0613 rows=1) (actual time=0.0138..0.0172 rows=2 loops=1)\n Hence, this is a very low priority bug. Verified for 8.0 and all higher and supported versions.
[20 Nov 2024 2:55]
Woson Wong
I test "explain FORMAT=TREE" and "explain analyze", also get different cost in force index situation. Both queries use the same index, but get different cost values in explain result. 1.1 explain analyze select * from test_ddl force index (idx_gmt_create) where addr="hangzhou" order by gmt_create limit 1; Result: | -> Limit: 1 row(s) (cost=0.0613 rows=0.1) (actual time=0.383..0.383 rows=1 loops=1) -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0613 rows=0.1) (actual time=0.365..0.365 rows=1 loops=1) -> Index scan on test_ddl using idx_gmt_create (cost=0.0613 rows=1) (actual time=0.327..0.341 rows=2 loops=1) | 1.2 explain analyze select * from test_ddl where addr="hangzhou" order by gmt_create limit 1; Result: | -> Limit: 1 row(s) (cost=0.0421 rows=0.718) (actual time=0.441..0.441 rows=1 loops=1) -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0421 rows=0.718) (actual time=0.357..0.357 rows=1 loops=1) -> Index scan on test_ddl using idx_gmt_create (cost=0.0421 rows=2) (actual time=0.312..0.313 rows=2 loops=1) | 2.1 explain FORMAT=TREE select * from test_ddl force index (idx_gmt_create) where addr="hangzhou" order by gmt_create limit 1; Result: | -> Limit: 1 row(s) (cost=0.0613 rows=0.1) -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0613 rows=0.1) -> Index scan on test_ddl using idx_gmt_create (cost=0.0613 rows=1) | 2.2 explain FORMAT=TREE select * from test_ddl where addr="hangzhou" order by gmt_create limit 1; Result: | -> Limit: 1 row(s) (cost=0.0421 rows=0.718) -> Filter: (test_ddl.addr = 'hangzhou') (cost=0.0421 rows=0.718) -> Index scan on test_ddl using idx_gmt_create (cost=0.0421 rows=2) |
[20 Nov 2024 11:02]
MySQL Verification Team
Thank you, Mr. Wong.