MIN / MAX vs ORDER BY and LIMIT

Question

Of the following queries, which method do you consider the best? What are your reasons (code efficiency, improved maintainability, less WTFery) ...

SELECT MIN(`field`) FROM `tbl`; SELECT `field` FROM `tbl` ORDER BY `field` LIMIT 1; 

Answer 1

In the worst case, when you look at an unindexed field, using MIN() requires one full pass of the table. Using SORT and LIMIT requires file management. If you work with a large table, there will likely be a significant difference in performance. As a meaningless data point, MIN() took 0.36 s, and SORT and LIMIT took 0.84 from the 106,000 row table on my dev server.

If, however, you look at the indexed column, the difference becomes more difficult to notice (a pointless data point is 0.00 s in both cases). However, looking at the result of the explanation, it looks like MIN() can simply cut the smallest value from the index (“Select tables optimized” and “NULL”), while SORT and LIMIT still need to perform an ordered index traversal (106,000 rows) . The actual performance impact is likely to be negligible.

MIN() seems to be the way to go - it is faster in the worst case, indistinguishable in the best case, is standard SQL and most clearly expresses the value you are trying to get. The only case where it seems that using SORT and LIMIT would be desirable was indicated in mson , where you write a general operation that finds upper or lower N values ​​from arbitrary columns and it is not worth writing a special case operation.

Answer 2

 SELECT MIN(`field`) FROM `tbl`; 

Just because it is compatible with ANSI. Limit 1 refers to MySql as TOP for SQL Server.

Answer 3

As mson and Sean Maxsom , MIN was preferred.

Another reason ORDER BY + LIMIT is useful is if you want to get the value of a different column than the MIN column.

Example:

 SELECT some_other_field, field FROM tbl ORDER BY field LIMIT 1 

Answer 4

I think the answers depend on what you do.

If you have a query with 1 pin and the intention is as simple as you indicated, select min (field).

However, it usually happens that these types of requirements change in - capture top results, capture nth-mth results, etc.

I don’t think it’s too terrible an idea to commit your chosen database. Changing dbs should not be easy, and you will have to review the price you pay when you do this.

Why limit yourself now, for pain that you may or may not feel later?

I think it's best to stay ANSI as much as possible, but this is just a guideline ...

Answer 5

Given acceptable performance, I would use the former because it is semantically closer to intent.
If performance was a problem, (most modern optimizers will optimally optimize as the same query plan, although you need to check to verify this), then of course I would use a faster one.