Getting a Column's Max Value

Question

Is there any tangible difference (speed/efficiency) between these statements? Assume the column is indexed.

SELECT MAX(someIntColumn) AS someIntColumn

or

SELECT someIntColumn ORDER BY someIntColumn DESC LIMIT 1

Answer 1

EDIT based on explain plan:

Explain plan shows that max(column) is more efficient. The explain plan say, “Select tables optimized away”.

EXPLAIN SELECT version from schema_migrations order by version desc limit 1;
+----+-------------+-------------------+-------+---------------+--------------------------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys | key                      | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+---------------+--------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | schema_migrations | index | NULL          | unique_schema_migrations | 767     | NULL |    1 | Using index | 
+----+-------------+-------------------+-------+---------------+--------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

EXPLAIN SELECT max(version) FROM schema_migrations ;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

Answer 2

This depends largely on the query optimizer in your SQL implementation. At best, they will have the same performance. Typically, however, the first query is potentially much faster.

The first query essentially asks for the DBMS to inspect every value in someIntColumn and pick the largest one.

The second query asks the DBMS to sort all the values in someIntColumn from largest to smallest and pick the first one. Depending on the number of rows in the table and the existence (or lack thereof) of an index on the column, this could be significantly slower.

If the query optimizer is sophisticated enough to realize that the second query is equivalent to the first one, you are in luck. But if you retarget your app to another DBMS, you might get unexpectedly poor performance.