Description:
Two semantically equivalent queries show a large and stable performance difference on MySQL 9.7.1.
The second query only adds a redundant duplicate disjunct `z < 85` to the beginning of the OR expression:
A OR z < 85
z < 85 OR A OR z < 85
These predicates are logically equivalent. Both queries return the same result. However, MySQL evaluates the expensive expression `REPEAT(s, n)` in the first query, while the second query can short-circuit on the cheap predicate `z < 85`.
On my local MySQL 9.7.1 Docker setup, the first query takes about 96 ms, while the equivalent rewritten query takes about 0.6 ms, a ~158x difference.
Observed timing on MySQL 9.7.1:
Query 1 median: 95.98 ms
Query 2 median: 0.61 ms
Ratio: about 158x
How to repeat:
DROP DATABASE IF EXISTS mysql_or_duplicate_perf;
CREATE DATABASE mysql_or_duplicate_perf;
USE mysql_or_duplicate_perf;
CREATE TABLE t (
s VARCHAR(10),
n INT,
z INT
);
CREATE TABLE d (
n INT
);
INSERT INTO d VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT INTO t
SELECT
'x',
50000,
0
FROM d AS a
JOIN d AS b
JOIN d AS c;
-- Query 1: slower
SELECT COUNT(*)
FROM (
SELECT REPEAT(s, n) AS q4, z
FROM t
) AS sq
WHERE
((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL)
OR z < 85;
-- Query 2: equivalent but much faster
SELECT COUNT(*)
FROM (
SELECT REPEAT(s, n) AS q4, z
FROM t
) AS sq
WHERE
z < 85
OR ((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL)
OR z < 85;
Description: Two semantically equivalent queries show a large and stable performance difference on MySQL 9.7.1. The second query only adds a redundant duplicate disjunct `z < 85` to the beginning of the OR expression: A OR z < 85 z < 85 OR A OR z < 85 These predicates are logically equivalent. Both queries return the same result. However, MySQL evaluates the expensive expression `REPEAT(s, n)` in the first query, while the second query can short-circuit on the cheap predicate `z < 85`. On my local MySQL 9.7.1 Docker setup, the first query takes about 96 ms, while the equivalent rewritten query takes about 0.6 ms, a ~158x difference. Observed timing on MySQL 9.7.1: Query 1 median: 95.98 ms Query 2 median: 0.61 ms Ratio: about 158x How to repeat: DROP DATABASE IF EXISTS mysql_or_duplicate_perf; CREATE DATABASE mysql_or_duplicate_perf; USE mysql_or_duplicate_perf; CREATE TABLE t ( s VARCHAR(10), n INT, z INT ); CREATE TABLE d ( n INT ); INSERT INTO d VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); INSERT INTO t SELECT 'x', 50000, 0 FROM d AS a JOIN d AS b JOIN d AS c; -- Query 1: slower SELECT COUNT(*) FROM ( SELECT REPEAT(s, n) AS q4, z FROM t ) AS sq WHERE ((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL) OR z < 85; -- Query 2: equivalent but much faster SELECT COUNT(*) FROM ( SELECT REPEAT(s, n) AS q4, z FROM t ) AS sq WHERE z < 85 OR ((q4 = 'sample_35' OR q4 <> 'sample_12') AND z IS NOT NULL) OR z < 85;