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

I'm using the employees test database, available here:

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);

CodePudding user response：

    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
    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;