Mysql – Left Join 3 tables into ONE in Mysql

join;MySQLoptimization

I have 3 tables: Employees, attendanceIn table, attendanceOut
Database schema

And My query is

SELECT  employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,
       attendanceOut.attOutAS attout
    FROM  employees
    LEFT JOIN  attendanceIn  ON employees.eno=attendanceIn.eno
    LEFT JOIN  attendanceOut  ON employees.eno=attendanceOut.eno
    WHERE  DATE(attendanceIn.puchtime) LIKE '2016-07-01%'
      AND  attendanceOut.puchtime LIKE '2016-07-01%'
    GROUP BY  eno
    ORDER BY  eno ASC

Is there any better way to write the query?

Best Answer

Do not use functions on your columns you are comparing against, as that prevents the query from using any indexes you may have.

Start with this:

SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout
FROM employees 
LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno 
LEFT JOIN attendanceOut ON employees.eno=attendanceOut.eno
WHERE attendanceIn.puchtime >= '2016-07-01 00:00:00'
AND attendanceIn.puchtime < '2016-07-02 00:00:00'
AND attendanceOut.puchtime >= '2016-07-01 00:00:00' 
AND attendanceOut.puchtime < '2016-07-02 00:00:00' 
GROUP BY eno 
ORDER BY eno ASC

If you still need performance improvements, provide SHOW CREATE TABLE tablename statements for all your tables in this query.

Related Question