Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.4, 11.5
-
None
Description
In the example below, the query uses both table keys (using union(a,b)), but none of them is included into INDEX_STATISTICS.
SET @userstat.save= @@userstat; |
|
|
CREATE TABLE t (a INT, b INT, KEY(a), KEY(b)) ENGINE=MyISAM; |
INSERT INTO t VALUES (1,10),(2,20),(3,30),(4,40),(5,50); |
SET GLOBAL userstat= 1; |
FLUSH INDEX_STATISTICS;
|
EXPLAIN SELECT * FROM t WHERE a = 2 OR b = 30; |
SELECT * FROM t WHERE a = 2 OR b = 30; |
SHOW INDEX_STATISTICS;
|
|
|
# Cleanup
|
DROP TABLE t; |
SET GLOBAL userstat= @userstat.save; |
|
10.4 662bb176b412993a085fe329af559ddc3dc83ec3 |
EXPLAIN SELECT * FROM t WHERE a = 2 OR b = 30; |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where |
SELECT * FROM t WHERE a = 2 OR b = 30; |
a b
|
2 20
|
3 30
|
SHOW INDEX_STATISTICS;
|
Table_schema Table_name Index_name Rows_read
|
DROP TABLE t; |
If, however, the query is run with index_merge_union=off and uses sort_union(a,b) instead, both indexes appear in INDEX_STATISTICS:
SET optimizer_switch='index_merge_union=off'; |
EXPLAIN SELECT * FROM t WHERE a = 2 OR b = 30; |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t index_merge a,b a,b 5,5 NULL 2 Using sort_union(a,b); Using where |
SELECT * FROM t WHERE a = 2 OR b = 30; |
a b
|
2 20
|
3 30
|
SHOW INDEX_STATISTICS;
|
Table_schema Table_name Index_name Rows_read
|
test t b 1
|
test t a 1
|
DROP TABLE t; |