Press enter to see results or esc to cancel.

Optimizing MIN and MAX MySQL Functions

MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below:

If I want to get the MAX value for cn_id, I’d to a query like this which will be a full table scan:

If I add an index on cn_id, the query is optimized internally:
What if I’d want to get MAX(cn_id) for a particular u_id? Well, another full table scan!
Again the solution is simple, make sure that cn_id is part of an index including the u_id, in this case I created a composite key on (u_id, cn_id) so that once the optimizer found index records for u_id = 106476, within the same index record are the cn_id values.
Till next time!
UPDATE: Based on Daniel’s comment, to demonstrate the internal optimization for MIN and MAX with the proper keys in place, here is a sample query executed: