max vs limit which is more effective

Question

This might be a very simple question but didn't find the perfect answer. The query is to find the 2nd highest salary which can be done by using max and limit both..

Using MAX

 select max(salary) from table1 where 
 salary < (select max(salary) from table1);

Using limit

select salary from table1 where 
salary < (select max(salary) from table1)
order by salary desc
limit 1;

So which query will be better and less time consuming considering there are 1000's of records.

Thanks in advance.

Answer 1

Neither query is the right way to go about this. You are getting MAX(salary) from records with a salary less than MAX(salary) across all records. In other words, the second-highest salary. The way to do that is just this:

SELECT salary FROM table1 ORDER BY salary DESC LIMIT 1,1

If you really do want the max, just do

SELECT MAX(salary) FROM table1

There's no need for subqueries here. Regardless, make sure you have indexed the salary column, or the query will be slow no matter how you run it.

Answer 2

In my opinion the first query with MAX would be more efficient.

The second query requires sorting of the results, which has its own overhead. And I have seen that aggregation queries are in general more efficient than select all queries.

Disclaimer: This is based solely upon my understanding of SQL queries, but I haven't done any benchmarking.