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.

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

thanks for share this information it will helps to me/

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.