| Bug #120821 | SELECT DISTINCT makes an equivalent query ~170x faster on round90.t1 in MySQL 9.5.0 | ||
|---|---|---|---|
| Submitted: | 1 Jul 8:16 | Modified: | 8 Jul 11:11 |
| Reporter: | cl hl | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 9.5.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Jul 8:23]
Chaithra Marsur Gopala Reddy
Hi cl hl, We cannot repeat the problem without the actual data in the tables. Could you please provide the data for the table so that we can reproduce the problem at our end. Thanks, Chaithra
[5 Jul 12:26]
cl hl
The total SQL used to reproduce this bug:
DROP DATABASE IF EXISTS round90_repro_small;
CREATE DATABASE round90_repro_small;
USE round90_repro_small;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 VARCHAR(255) NOT NULL,
c3 VARCHAR(255) NULL,
c4 INT NULL,
c5 DATE NOT NULL,
c6 VARCHAR(10) NOT NULL,
PRIMARY KEY (c1)
);
CREATE INDEX idx_t1_c6_c5 ON t1 (c6, c5);
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (1, 'a', 'b', 1, '2025-01-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (2, 'a', 'b', 1, '2025-02-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3, 'a', 'b', 1, '2025-03-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (4, 'a', 'b', 1, '2025-04-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (5, 'a', 'b', 1, '2025-05-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (6, 'a', 'b', 1, '2025-06-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (7, 'a', 'b', 1, '2025-07-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (8, 'a', 'b', 1, '2025-08-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9, 'a', 'b', 1, '2025-09-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (10, 'a', 'b', 1, '2025-10-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (11, 'a', 'b', 1, '2025-11-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (12, 'a', 'b', 1, '2025-12-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (13, 'a', 'b', 1, '2026-01-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (14, 'a', 'b', 1, '2026-02-01', 'x');
-- original
SELECT subq.c1 % subq.c1 AS col_1
FROM (
SELECT s906.c3 AS c3, s906.c1 AS c1, s906.c5 AS c5
FROM t1 AS s906
WHERE s906.c5 < '2023-01-01 04:13:04'
) AS subq
WHERE CHAR_LENGTH(REPEAT(subq.c1, subq.c5)) <= 70;
-- mutated
SELECT subq.c1 % subq.c1 AS col_1
FROM (
SELECT DISTINCT s906.c3 AS c3, s906.c1 AS c1, s906.c5 AS c5
FROM t1 AS s906
WHERE s906.c5 < '2023-01-01 04:13:04'
) AS subq
WHERE CHAR_LENGTH(REPEAT(subq.c1, subq.c5)) <= 70;
[8 Jul 11:10]
Chaithra Marsur Gopala Reddy
Updated test case:
DROP DATABASE IF EXISTS round90_repro_small;
CREATE DATABASE round90_repro_small;
USE round90_repro_small;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 VARCHAR(255) NOT NULL,
c3 VARCHAR(255) NULL,
c4 INT NULL,
c5 DATE NOT NULL,
c6 VARCHAR(10) NOT NULL,
PRIMARY KEY (c1)
);
CREATE INDEX idx_t1_c6_c5 ON t1 (c6, c5);
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (1, 'a', 'b', 1, '2025-01-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (2, 'a', 'b', 1, '2025-02-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (3, 'a', 'b', 1, '2025-03-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (4, 'a', 'b', 1, '2025-04-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (5, 'a', 'b', 1, '2025-05-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (6, 'a', 'b', 1, '2025-06-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (7, 'a', 'b', 1, '2025-07-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (8, 'a', 'b', 1, '2025-08-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9, 'a', 'b', 1, '2025-09-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (10, 'a', 'b', 1, '2025-10-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (11, 'a', 'b', 1, '2025-11-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (12, 'a', 'b', 1, '2025-12-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (13, 'a', 'b', 1, '2026-01-01', 'x');
INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (14, 'a', 'b', 1, '2026-02-01', 'x');
-- original
SELECT subq.c1 % subq.c1 AS col_1
FROM (
SELECT s906.c3 AS c3, s906.c1 AS c1, s906.c5 AS c5
FROM t1 AS s906
WHERE s906.c5 < '2023-01-01 04:13:04'
) AS subq
WHERE CHAR_LENGTH(REPEAT(subq.c1, subq.c5)) <= 70;
-- mutated
SELECT subq.c1 % subq.c1 AS col_1
FROM (
SELECT DISTINCT s906.c3 AS c3, s906.c1 AS c1, s906.c5 AS c5
FROM t1 AS s906
WHERE s906.c5 < '2023-01-01 04:13:04'
) AS subq
WHERE CHAR_LENGTH(REPEAT(subq.c1, subq.c5)) <= 70;
[8 Jul 11:11]
Chaithra Marsur Gopala Reddy
Hi cl hl, Thank you for the test case. Verified as described. We do not see the problem with Hypergraph Optimizer. So a possible workaround could be use that. Thanks, Chaithra
Description: On the same dataset, the original query and the DISTINCT variant return the same result set, but the DISTINCT variant runs much faster. The optimizer estimate goes in the opposite direction: the DISTINCT version has a higher estimated cost, yet the measured runtime is dramatically lower. Observed Result Both queries return the same result set on round90. Row count: 0 Performance Original: median 74.264 ms, average 74.537 ms Mutated: median 0.434 ms, average 0.568 ms Ratio: 171.1613 EXPLAIN FORMAT=JSON Summary Original plan:Covering index scan on t1 using idx_t1_c6_c5 Filter estimated_total_cost = 1.6499999090881587 Mutated plan:Table scan on t1 Filter Materialize Table scan on subq estimated_total_cost = 5.275161085431277 Expected Result The optimizer should not choose a plan that is dramatically slower for the original query when the rewritten query is semantically equivalent on the same dataset. At minimum, the cost model should not be so far from runtime reality for this case. Actual Result The version without DISTINCT is much slower in practice, even though the optimizer estimates it as cheaper. The DISTINCT rewrite is about 170x faster on the same data. How to repeat: CREATE DATABASE IF NOT EXISTS round90; USE round90; CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, c2 VARCHAR(255) NOT NULL, c3 VARCHAR(255) NULL, c4 INT NULL, c5 DATE NOT NULL, c6 VARCHAR(10) NOT NULL, PRIMARY KEY (c1) ); CREATE UNIQUE INDEX idx_t1_pk ON t1 (c1); CREATE INDEX idx_t1_c6 ON t1 (c6); CREATE INDEX idx_t1_c6_c5 ON t1 (c6, c5); CREATE INDEX idx_t1_c3_c2 ON t1 (c3(63), c2(63)); -- original SELECT subq.c1 % subq.c1 AS col_1 FROM ( SELECT s906.c3 AS c3, s906.c1 AS c1, s906.c5 AS c5 FROM t1 AS s906 WHERE s906.c5 < '2023-01-01 04:13:04' ) AS subq WHERE CHAR_LENGTH(REPEAT(subq.c1, subq.c5)) <= 70; -- mutated SELECT subq.c1 % subq.c1 AS col_1 FROM ( SELECT DISTINCT s906.c3 AS c3, s906.c1 AS c1, s906.c5 AS c5 FROM t1 AS s906 WHERE s906.c5 < '2023-01-01 04:13:04' ) AS subq WHERE CHAR_LENGTH(REPEAT(subq.c1, subq.c5)) <= 70;