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.
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
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 🙂