MAX()is a simple column reference that is part of a key.
WHEREclause or the
WHEREis used with a constant for all prefix parts of the key before the argument to
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
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
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:
GROUP BYpart only uses indexed columns in the same order as in the index.
MAX()functions and all of them using the same column which is the next index part after the used
Loose scan will apply for your query if
Using index for group-by in the
Extra column. 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
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.