Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.9, 10.1
-
None
Description
After the upgrade from 10.1.8 to 10.1.9 or 10.1.10, frequent queries involving index merge went crazy.
Here is a repeatable test case :
DROP TABLE if exists t; |
CREATE TABLE t |
(
|
id bigint PRIMARY KEY AUTO_INCREMENT, |
a bigint, |
b int, |
c bigint, |
KEY(a), KEY(b), KEY(c) |
) ENGINE = InnoDB;
|
|
|
INSERT INTO t(a, b, c) |
VALUES (rand() * 44, rand() * 283, rand() * 131072); |
|
|
INSERT INTO t(a, b, c) |
VALUES (rand() * 44, rand() * 283, rand() * 131072); |
|
|
REPLACE INTO t(a, b, c) |
SELECT rand() * 44, rand() * 283, rand() * 131072 |
FROM t t1, |
t t2,
|
t t3,
|
t t4,
|
t t5,
|
t t6,
|
t t7,
|
t t8,
|
t t9,
|
t t10,
|
t t11,
|
t t12,
|
t t13,
|
t t14,
|
t t15,
|
t t16,
|
t t17;
|
ANALYZE TABLE t; |
|
|
EXPLAIN
|
SELECT DISTINCT c |
FROM t |
WHERE b = 5 AND a = 10\G |
10.1.8 :
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: index_merge
|
possible_keys: a,b
|
key: b,a
|
key_len: 5,9
|
ref: NULL
|
rows: 10
|
Extra: Using intersect(b,a); Using where; Using temporary
|
1 row in set (0.00 sec)
|
10.1.9 :
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t
|
type: index
|
possible_keys: a,b
|
key: c
|
key_len: 9
|
ref: NULL
|
rows: 131109
|
Extra: Using where
|
1 row in set (0.00 sec)
|