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
2 个解决方案
#1
6
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.
这在很大程度上取决于SQL实现中的查询优化器。充其量，他们将具有相同的表现。但是，通常情况下，第一个查询可能要快得多。
The first query essentially asks for the DBMS to inspect every value in someIntColumn and pick the largest one.
第一个查询实质上要求DBMS检查someIntColumn中的每个值并选择最大的值。
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.
第二个查询要求DBMS将someIntColumn中的所有值从最大到最小排序，然后选择第一个。根据表中的行数以及列上索引的存在（或缺少），这可能会明显变慢。
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.
如果查询优化器足够复杂以实现第二个查询等同于第一个查询，那么您很幸运。但是，如果您将应用程序重新定位到另一个DBMS，则可能会出现意外性能不佳的情况。
#2
0
EDIT based on explain plan:
编辑基于解释计划：
Explain plan shows that max(column) is more efficient. The explain plan say, “Select tables optimized away”.
解释计划显示max（列）更有效。解释计划说，“选择优化的表格”。
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)
#1
6
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.
这在很大程度上取决于SQL实现中的查询优化器。充其量，他们将具有相同的表现。但是，通常情况下，第一个查询可能要快得多。
The first query essentially asks for the DBMS to inspect every value in someIntColumn and pick the largest one.
第一个查询实质上要求DBMS检查someIntColumn中的每个值并选择最大的值。
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.
第二个查询要求DBMS将someIntColumn中的所有值从最大到最小排序，然后选择第一个。根据表中的行数以及列上索引的存在（或缺少），这可能会明显变慢。
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.
如果查询优化器足够复杂以实现第二个查询等同于第一个查询，那么您很幸运。但是，如果您将应用程序重新定位到另一个DBMS，则可能会出现意外性能不佳的情况。
#2
0
EDIT based on explain plan:
编辑基于解释计划：
Explain plan shows that max(column) is more efficient. The explain plan say, “Select tables optimized away”.
解释计划显示max（列）更有效。解释计划说，“选择优化的表格”。
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)