MIN/MAX optimization

MariaDB and MySQL can optimize a MIN() and MAX() founctions to be a single row lookup in the following cases:

• There is only one table used in the SELECT.
• You only have constants, MIN() and MAX() in the SELECT part.
• The argument to MIN() and MAX() is a simple column reference that is part of a key.
• There is no WHERE close or the WHERE is used with constant for all prefix parts of the key before the argument to MIN()/MAX().
• If the argument used in the WHERE clause, it can be be compared to a constant with < or <= in case of MAX() and with > or >= in case of MIN().

Here are some examples to clarify this. In this case we assume there is an index on columns (a,b,c)

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=const the condition a IS NULL can be used.

The above optimization also works for sub queries:

SELECT x from t2 where y= (SELECT MIN(b) FROM t1 WHERE a=const)

See also

• MIN()
• MAX()