Hello,
I've built a timekeeping aplication from a fingerprint machine with automatic import to db and I'm getting some reports.
One report is that I'm getting the employees time if they are late from work.
But my table is small with 20.000 entries, but with the following query, the results are generating in 9.7 seconds, and its really slow.
I have to use INNER JOIN and some subselects for getting the minimal hour and other stuff.
Could I optimize this query more ?
`SELECT p.data,u.name, SEC_TO_TIME(AVG(TIME_TO_SEC(p.ora))) as timp, u.user, p.tip, u.program_de_la FROM utilitar.pontaj as p
INNER JOIN utilitar.pontaj_usr as u ON u.user = p.user
WHERE p.user NOT IN('000127','000126')
AND p.data = '2012-05-08' AND (SELECT MIN(ora) FROM utilitar.pontaj WHERE data = '2012-05-08' AND user = p.user) > u.program_de_la AND p.tip in('A','C')
AND u.program_de_la NOT IN('','-')
AND u.program_pana_la NOT IN('','-' )
GROUP BY p.user ORDER BY timp ASC, u.name ASC`
Results
"data" "name" "timp" "user" "tip" "program_de_la"
"2012-05-08" "xxxxxxxxxxxxxxxxxxxx " "07:08:00" "000030" "A" "07:00"
"2012-05-08" "34tgyhhgddfgdgdf" "08:32:00" "000001" "A" "08:30"
"2012-05-08" "34tgyhhgddfgd" "08:33:00" "000132" "A" "08:30"
"2012-05-08" "34tgyhhgddfgdgdf" "08:34:00" "000187" "A" "08:30"
"2012-05-08" "34tgyhhgddfgdgdf" "08:38:00" "000039" "A" "08:30"
"2012-05-08" "34tgyhhgddfgdgdf" "09:14:00" "000046" "A" "08:30"
"2012-05-08" "34tgyhhgddfgd" "14:23:00" "000101" "A" "14:00"
The results are correct but a little slow.
