125

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;
nickf
  • 537,072
  • 198
  • 649
  • 721

6 Answers6

153

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 an anecdotal 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.

Mark
  • 999
  • 9
  • 19
Sean McSomething
  • 6,376
  • 2
  • 23
  • 28
  • 13
    o(n) for one single pass vs 0(nlogn) for sorting – Abhishek Iyer Apr 25 '13 at 22:12
  • 1
    @AbhishekIyer you are totally right, but I would add "in the worst case for unindexed field". – dmikam Feb 17 '15 at 08:51
  • That part about worst unindexed case is wrong. You always need a full scan, how else you know it's a min or max? It's not like you're scanning and the value screams: "Hey, you finally found me! I'm Jack, the max!". – Robo Robok Sep 22 '19 at 13:46
  • In a test with an indexed table with 470 million rows, both queries take 0.00 s. However, if we add to the queries a filter "WHERE field2=x", the query with LIMIT still takes 0.00 s and the query with MIN takes 0.21 s. – Antonio Cañas Vargas May 30 '20 at 13:01
  • This depends on your table and database setup. We just reduced a DB with >10M rows from multi-second to sub-second by pivoting from `order by` with `limit` to `group by` with `max`. The logic should be easy to comprehend. Iteration aside, you either need to retrieve n rows, or 1 row. – MrMesees Aug 23 '21 at 11:56
15
SELECT MIN(`field`)
FROM `tbl`;

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

awesoon
  • 32,469
  • 11
  • 74
  • 99
Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
  • Most DBMSes have limit/offset or equivalent, and it is used in the majority of apps I have worked on (not as an alternative to MIN, but for other purposes such as pagination.) – finnw Jan 09 '09 at 01:36
  • @finnw - I agree, but the questioner's example was comparing limit with min explicitly. – Otávio Décio Jan 09 '09 at 01:41
11

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
Community
  • 1
  • 1
user650654
  • 5,630
  • 3
  • 41
  • 44
  • 1
    Is this preferable in this case? or using nested queries with max/min? (`SELECT some_other_field, field FROM tbl WHERE field = ( SELECT MIN(field) FROM tbl )`) – Sourav Kannantha B May 07 '21 at 13:57
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...

mson
  • 7,762
  • 6
  • 40
  • 70
4

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.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
0

user650654 said that ORDER BY with LIMIT 1 useful when one need "to get the value of a different column than the MIN column". I think, in this case we still have better performance with two single passes using MIN instead of sorting (hoping this is optimized :()

SELECT some_other_field, field
FROM tbl
WHERE field=(SELECT MIN(field) FROM tbl)
Ivan Vinitskyi
  • 71
  • 1
  • 2
  • The question itself assumes that [field] is unique... Otherwise there might be multiple records with the same value of field, with different values for some_other_field. The query would return multiple rows. in That case, you would want to limit the output to one of those rows using some other criterion. – Charles Bretana Sep 08 '22 at 13:28
  • 1
    Oh, OK. One may add "limit 1" in addition. – Ivan Vinitskyi Sep 09 '22 at 17:53