> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/minmax-optimization.md).

# MIN/MAX optimization

## Min/Max optimization without GROUP BY

MariaDB and MySQL can optimize the [MIN()](/docs/server/reference/sql-functions/aggregate-functions/min.md) and [MAX()](/docs/server/reference/sql-functions/aggregate-functions/max.md) functions 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` clause or the `WHERE` is used with a constant for all prefix parts of the key before the argument to `MIN()`/`MAX()`.
* If the argument is used in the `WHERE` clause, it can 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)`

```sql
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 [subqueries](/docs/server/reference/sql-statements/data-manipulation/selecting-data/subqueries.md):

```sql
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:

```sql
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.
* The `GROUP BY` part only uses indexed columns in the same order as in the index.
* The only aggregated functions in the `SELECT` part are `MIN()` and `MAX()` functions and all of them using the same column which is the next index part after the used `GROUP BY` columns.
* Partial indexed columns cannot be used (like only indexing 10 characters of a `VARCHAR(20)` column).

Loose scan will apply for your query if [EXPLAIN](/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain.md) shows `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 `MIN()` or `MAX()` for every unique key prefix.

The following examples assume that the table `t1` has an index on `(a,b,c)`.

```sql
SELECT a, b, MIN(c),MAX(c) FROM t1 GROUP BY a,b
```

## See also

* [MIN()](/docs/server/reference/sql-functions/aggregate-functions/min.md)
* [MAX()](/docs/server/reference/sql-functions/aggregate-functions/max.md)
* [MySQL manual on loose index scans](https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}