CODEWITHSUNDEEP
mysqlsubqueryworkbench
gpelelis
gpelelis

Reputation: 148

reuse computated data from a subquery

First things first, i am new to SQL. So I've got two tables dept_emp and titles. My problem is that when I have a single select query like this, I need ~2secs(I am ok with that)

select  dept_no 
from dept_emp,titles
where titles.emp_no = dept_emp.emp_no
    and titles.title = 'Engineer'
group by dept_emp.dept_no
having count(*) < 1000);

When I use that as a subquery on something like that

select dept_emp.emp_no
from dept_emp
where dept_emp.dept_no in (
    select  dept_no 
    from dept_emp,titles
    where titles.emp_no = dept_emp.emp_no
        and titles.title = 'Engineer'
    group by dept_emp.dept_no
    having count(*) < 1000)
limit 10;

execution time goes around 100 sec. If my limit goes on 1000+ data, then execution time goes to infinity..

What could I do to reuse the data that I use from the subquery? And lastly would be more efficient if I could convert that inner query to a join?

Upvotes: 0

Views: 399

Answers (1)

Strawberry
Strawberry

Reputation: 33945

Make sure dept_emp.dept_no is indexed (and (emp_no,title) too)...

SELECT x.emp_no
  FROM dept_emp x
  JOIN 
     ( SELECT dept_no 
         FROM dept_emp de
         JOIN titles t   
           ON t.emp_no = de.emp_no
        WHERE t.title = 'Engineer'
        GROUP 
           BY de.dept_no
       HAVING COUNT(*) < 1000
     ) y
    ON y.dept_no = x.dept_no
 ORDER 
    BY emp_no
 LIMIT 10;

Upvotes: 1

Related Questions