Is using of subqueries more efficient than using Limit offset and order by in MySQL?

All we need is an easy explanation of the problem, so here it is.

For instance, we have a problem of finding the employee with the second highest salary in the table.
This is my table

id  name    dept    salary
1   Ram     HR      10000
2   Amrit   MRKT    20000
3   Ravi    HR      30000
4   Nitin   MRKT    40000
5   Varun   IT      50000

Then I would write a subquery like this.

select e_name,salary from employee
    where salary = (select max(salary) from employee
                    where salary <> (select max(salary) from employee));

And I would use limit and offset like this:

select e_name, salary from employee order by salary desc limit 1 offset 1;

Which of the following would be more efficient? and why?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

There’s a lot of variables when it comes to performance and query tuning, so there’s no guaranteed answer to which one will be more performant for your data at any given time without testing and observing their query plans.

But in general, the second example using LIMIT and OFFSET is likely to be more efficient since it only queries the employee table once as opposed to three times in your first example.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0