Which Is More Efficient In Sql Min Or Top

which is more efficient in sql min or top? select MIN(salary) from ABC select top 1 salary from ABC order by salary asc

Solution 1:

Best way would be looking at execution plans, results may vary of course.

I've tried that on my current environment and MIN() seems to work much faster, in first scenario SORT BY eats almost everything.

When using MIN() Index scan takes 96% of execution time, which is good. Table contains around 1bil records.

Solution 2:

You are looking for the minimum salary. So use MIN(salary); it is made for exactly this purpose. Your query is thus as readable and maintainable as possible. I have never seen anybody run in performance problems because of using MIN or MAX.

Besides the task to find a minimum in a list is very simple. The task to sort a list (only to keep the first line) is quite another. If you are lucky the DBMS sees through this and doesn't sort at all, but simply looks up the minimum value for you, and then you are where you were with MIN already.

If for some miracle MySQL performs the TOP 1 query faster than the MIN query, then consider this a flaw, stick with the MIN query though, and wait for a future version of MySQL to perform better :-)

Use TOP n queries only when you need more then the MIN or MAX value from the record.

Solution 3:

Tests made on sql server 2012 ( you didn't ask for a specific version, and this is what I have):

-- Create the tableCREATETABLE ABC (salary int)

-- insert sample dataDECLARE@Iint=0

WHILE @i<1000000-- that's right, a million records..BEGININSERTINTO ABC VALUES (@i)
   SET@I=@I+1END

including execution plans and run both queries:

selectMIN(salary) from ABC
select top 1 salary from ABC orderby salary asc

Results:

• Without indexes: query cost for top 1 was 94% and for min was 6%.
• With an index on salary - query cost for both was 50% (doesn't matter if the index is clustered or not).

Without an index:

With an index: (clustered and non-clustered resulted in the same execution plan)