How to optimize this SQL query which select from the same table twice

Question

I'm using the employees test database, available here: https://github.com/datacharmer/test_db

I'm trying to get names of employees who have the same last name as their manager. I wish to make the query below faster.

SELECT concat(first_name,' ',last_name) 
FROM  
((employees JOIN dept_emp ON employees.emp_no=dept_emp.emp_no) 
JOIN dept_manager ON dept_emp.dept_no=dept_manager.dept_no) 
WHERE employees.last_name=
(SELECT last_name FROM employees WHERE employees.emp_no=dept_manager.emp_no);

As you can see, there's a select in the where clause that searches the entire table. I assume that means for each row of the joined table, it will the entire employees table. I tried to solve it by creating a smaller table before joining, but it's even 4x slower.

SELECT concat(B.first_name,' ',B.last_name)
FROM 
(SELECT employees.emp_no, employees.last_name, dept_no 
FROM employees JOIN dept_manager ON employees.emp_no=dept_manager.emp_no) AS A
JOIN
(SELECT employees.first_name, employees.emp_no, last_name, dept_no 
FROM employees JOIN dept_emp ON  employees.emp_no=dept_emp.emp_no) AS B
ON (A.dept_no=B.dept_no AND A.last_name=B.last_name);

Version 8.0, can you write up a query? Indexing obviously makes things faster, so I'm just looking for a query improvement — Huy Le
– Huy Le, Commented Nov 7, 2021 at 2:00
Can a department have more than one manager? If not, get rid of table dept_manager and add dept_emp_no in table departments. If you are trying to keep a history of who worked for a dept when and track their movements? — Rick James
– Rick James, Commented Nov 7, 2021 at 3:41
Eh the schema is given, so I can't change that. Each departments can have more than 1 manager, but at different times (basically history) — Huy Le
– Huy Le, Commented Nov 7, 2021 at 4:04
You want names of current employees...? Is the to_date some time in the future? Or maybe NULL means "current"? If not, it is hard to optimize the query without knowing. — Rick James
– Rick James, Commented Nov 7, 2021 at 16:20

Firewizz · Accepted Answer · 2021-11-09 07:53:03Z

2

    SELECT 
    CONCAT_WS(' ', first_name, last_name) AS fullName
FROM employees 
    JOIN dept_emp ON dept_emp.emp_no=employees.emp_no
    JOIN dept_manager ON dept_manager.dept_no=dept_emp.dept_no
    # added after comment from Rick James
        AND dept_manager.from_date <= emp.end_date 
            AND emp.from_date <= dept_manager.to_date
    JOIN employees managers ON managers.emp_no=dept_manager.emp_no 
        AND managers.last_name=employees.lastname

From the top of my head. didnt test btw.

And add an index: last_name

ALTER TABLE `employees` 
ADD INDEX `idx_lastName`(`last_name`) USING BTREE;

edited Nov 9, 2021 at 7:53

answered Nov 5, 2021 at 10:37

Firewizz

8336 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Huy Le Over a year ago

in the last line, there's no table managers

Firewizz Over a year ago

@HuyLe i aliased the employees table as managers there. I just re-joined employees as managers

Firewizz Over a year ago

dofactory.com/sql/alias

Huy Le Over a year ago

On the 2nd to last line, the "AND dept_manager" is missing something. If I remove it, this query becomes just as slow as my slow version. If I add "dept_manager.emp_no=dept_emp.emp_no", then the result is incorrect (only show the managers)

Firewizz Over a year ago

Sorry, edited the answer. Also dont forget to add the index to last_name

|

Collectives™ on Stack Overflow

How to optimize this SQL query which select from the same table twice

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related