1

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.

3
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by maniat123
0

Got it working, I've put AND p.tip in('A','C') into the subselect and it worked instantly :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.