Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3.2
-
Ubuntu 22.04
Description
Hi, MariaDB developers. I found a missed optimization in MariaDB.
When a SELECT statement uses a HAVING clause without any GROUP BY or aggregate functions, the condition is semantically equivalent to a WHERE clause. However, the optimizer does not transform the HAVING condition into a table-access filter that can exploit indexes (e.g., PRIMARY KEY). Instead, it performs a full table scan and applies the filter afterward, leading to orders‑of‑magnitude worse performance compared to using an equivalent WHERE.
-- Index Not Used for HAVING Without GROUP BY or Aggregate Functions
|
CREATE TABLE t0(c0 INT8 PRIMARY KEY); |
INSERT INTO t0 SELECT seq FROM seq_1_to_1000000; |
|
|
SELECT * FROM t0 WHERE t0.c0 = 1; -- 0.000 sec |
SELECT * FROM t0 HAVING t0.c0 = 1; -- 0.085 sec |
|
|
EXPLAIN SELECT * FROM t0 WHERE t0.c0 = 1; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |
| 1 | SIMPLE | t0 | const | PRIMARY | PRIMARY | 8 | const | 1 | | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ |
|
|
EXPLAIN SELECT * FROM t0 HAVING t0.c0 = 1; |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+ |
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 998739 | | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+ |