There is min/max optimization in case of single SELECT, i.e:
We check only index for max (or min) value.
But if we have (probably via VIEW)
we get all tables in temporary table than scan it for max (min).
If we transform the above query to :
optimisation will be still possible.
The idea of optimization is to push MIN/MAX into each part of the UNION if possible (It might not be possible for some SELECTs in the UNION)
Optimization is not possible in case:
- underlying SELECT has:
- GROUP BY
- ORDER BY/LIMIT
- aggregate functions
- non numeric type of the MAX/MIN field
- outer SELECT has:
Also possible such transformation for COUNT (only for ALL UNION):