MIN/MAX vs ORDER BY and LIMIT

Question

Out of the following queries, which method would you consider the better one? What are your reasons (code efficiency, better maintainability, less WTFery)...

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

SELECT `field`
FROM `tbl`
ORDER BY `field`
LIMIT 1;

Answer 1

In the worst case, where you're looking at an unindexed field, using MIN() requires a single full pass of the table. Using SORT and LIMIT requires a filesort. If run against a large table, there would likely be a significant difference in percieved performance. As a meaningless data point, MIN() took .36s while SORT and LIMIT took .84s against a 106,000 row table on my dev server.

If, however, you're looking at an indexed column, the difference is harder to notice (meaningless data point is 0.00s in both cases). Looking at the output of explain, however, it looks like MIN() is able to simply pluck the smallest value from the index ('Select tables optimized away' and 'NULL' rows) whereas the SORT and LIMIT still needs needs to do an ordered traversal of the index (106,000 rows). The actual performance impact is probably negligible.

It looks like MIN() is the way to go - it's faster in the worst case, indistinguishable in the best case, is standard SQL and most clearly expresses the value you're trying to get. The only case where it seems that using SORT and LIMIT would be desirable would be, as mson mentioned, where you're writing a general operation that finds the top or bottom N values from arbitrary columns and it's not worth writing out the special-case operation.

Answer 2

As mson and Sean McSomething have pointed out, MIN is preferable.

One other reason where 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 3

Given acceptable performance I would use the first one because it is semantically closer to the intent.
If the performance was an issue, (Most modern optimizers will probalbly optimize both to the same query plan, although you have to test to verify that) then of course I would use the faster one.

Answer 4

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

Simply because it is ANSI compatible. Limit 1 is particular to MySql as TOP is to SQL Server.

Answer 5

I think the answers depends on what you are doing.

If you have a 1 off query and the intent is as simple as you specified, select min(field) is preferable.

However, it is common to have these types of requirements change into - grab top n results, grab nth - mth results, etc.

I don't think it's too terrible an idea to commit to your chosen database. Changing dbs should not be made lightly and have to revise is the price you pay when you make this move.

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

I do think it's good to stay ANSI as much as possible, but that's just a guideline...