Min/Max optimization without GROUP BY
- There is only one table used in the
- You only have constants,
- The argument to
MAX()is a simple column reference that is part of a key.
- There is no
WHEREclause or the
WHEREis used with a constant for all prefix parts of the key before the argument to
- If the argument is used in the
WHEREclause, it can be be compared to a constant with
<=in case of
>=in case of
Here are some examples to clarify this.
In this case we assume there is an index on columns
SELECT MIN(a),MAX(a) from t1 SELECT MIN(b) FROM t1 WHERE a=const SELECT MIN(b),MAX(b) FROM t1 WHERE a=const SELECT MAX(c) FROM t1 WHERE a=const AND b=const SELECT MAX(b) FROM t1 WHERE a=const AND b<const SELECT MIN(b) FROM t1 WHERE a=const AND b>const SELECT MIN(b) FROM t1 WHERE a=const AND b BETWEEN const AND const SELECT MAX(b) FROM t1 WHERE a=const AND b BETWEEN const AND const
- Instead of
a IS NULLcan be used.
The above optimization also works for subqueries:
SELECT x from t2 where y= (SELECT MIN(b) FROM t1 WHERE a=const)
Cross joins, where there is no join condition for a table, can also be optimized to a few key lookups:
select min(t1.key_part_1), max(t2.key_part_1) from t1, t2
Min/Max optimization with GROUP BY
MariaDB and MySQL support loose index scan, which can speed up certain
GROUP BY queries. The basic idea is that when scanning a
BTREE index (the most common index type for the MariaDB storage engines) we can jump over identical values for any prefix of a key and thus speed up the scan significantly.
Loose scan is possible in the following cases:
- The query uses only one table.
GROUP BYpart only uses indexed columns in the same order as in the index.
- The only aggregated functions in the
MAX()functions and all of them using the same column which is the next index part after the used
- Partial indexed columns cannot be used (like only indexing 10 characters of a
Loose scan will apply for your query if
Using index for group-by in the
In this case the optimizer will do only one extra row fetch to calculate the value for
MAX() for every unique key prefix.
The following examples assume that the table
t1 has an index on
SELECT a, b, MIN(c),MAX(c) FROM t1 GROUP BY a,b