0

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?

1

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.

  • The ` 00:00:00` can be removed without any impact. – Rick James Jul 11 '16 at 22:39
  • I prefer x >= '2016-07-01' AND x < '2016-07-01' + INTERVAL 1 DAY -- let the computer compute the ending date. – Rick James Jul 11 '16 at 22:40
  • @RickJames Good to know, thanks for the tip. – Willem Renzema Jul 12 '16 at 2:43
0

That should also work more efficient than Willem solution

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) = '2016-07-01'
AND DATE(attendanceOut.puchtime) = '2016-07-01' 
GROUP BY eno 
ORDER BY eno ASC
  • Punch TIme have an important role while calculating attendance. Moreover for companies having employees shifts. So we cannot omit punch time in the query – Avinash Jul 6 '16 at 8:04
  • 2
    Note that you brought back the deficiency that Willem has pointed out: by using functions you effectively disabled the usage of any index available on the punchtime-columns (unless there is a function-based one). – piet.t Jul 6 '16 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.