Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.2.2
-
None
-
Not for Release Notes
Description
Two logically equivalent AND predicates return the same result, but their execution times differ dramatically depending only on predicate order.
The slower query evaluates an expensive ALL subquery before checking a cheap predicate that is false for all rows. The faster query places the cheap false predicate first, so the expensive ALL subquery can be avoided by short-circuit evaluation.
In my test, both queries returned COUNT
= 0, but the first query took about 49–52 ms, while the second query took about 0.24–0.39 ms.
How to repeat
DROP DATABASE IF EXISTS perf_and_all_order;
|
CREATE DATABASE perf_and_all_order;
|
USE perf_and_all_order;
|
|
|
CREATE TABLE t(
|
n INT,
|
c VARCHAR(10), |
payload VARCHAR(10), |
len INT
|
);
|
|
|
CREATE TABLE d(n INT);
|
|
|
INSERT INTO d VALUES
|
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
|
|
INSERT INTO t
|
SELECT 1, 'x', 'x', 50000 |
FROM d a JOIN d b JOIN d c;
|
|
|
SET profiling = 1; |
|
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM t AS o
|
WHERE o.n <= ALL (
|
SELECT LENGTH(REPEAT(s.payload, s.len))
|
FROM t AS s
|
)
|
AND o.c = 'no_match'; |
|
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM t AS o
|
WHERE o.c = 'no_match' |
AND o.n <= ALL (
|
SELECT LENGTH(REPEAT(s.payload, s.len))
|
FROM t AS s
|
)
|
AND o.c = 'no_match'; |
|
|
SHOW PROFILES;
|
Observed result
Query 1 result: COUNT(*) = 0 |
Query 2 result: COUNT(*) = 0 |
|
|
Repeated test durations:
|
|
|
Query 1: |
0.04895754 sec |
0.04961830 sec |
0.05173770 sec |
|
|
Query 2: |
0.00038803 sec |
0.00024930 sec |
0.00023963 sec |
Expected result
MariaDB should avoid evaluating the expensive ALL subquery when a cheap AND predicate already rejects all rows, or otherwise reorder/simplify the predicates so the two logically equivalent queries have comparable execution time.
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-