Member Avatar for Szabi Zsoldos

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
  • 237 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by maniat123

Dani AI

Generated

Nice troubleshooting by — moving the selective predicate into the inner computation reduced the scanned set and made the query fast. 's tip to inspect the plan with EXPLAIN is the right first step: compare the reported rows and key usage before and after any change to verify the optimizer is using indexes.

Practical steps that materially help similar queries:

  • Add a composite index that matches the filters and the MIN scan. For example:

    CREATE INDEX idx_pontaj_data_user_ora ON pontaj (data, user, ora);

    If tip is frequently used to restrict the MIN calculation, a covering index that includes it can avoid extra lookups:

    CREATE INDEX idx_pontaj_data_tip_user_ora ON pontaj (data, tip, user, ora);

  • When a MIN-per-user is needed, compute it once in a derived table and join back, so the aggregation runs a single time instead of being (potentially) re-evaluated per outer row. Example pattern:

    SELECT d.data, u.name, p.user
FROM pontaj p
JOIN (
  SELECT user, data, MIN(ora) AS min_ora
  FROM pontaj
  WHERE data = '2012-05-08' AND tip IN ('A','C')
  GROUP BY user
) d ON p.user = d.user AND p.data = d.data AND p.ora = d.min_ora
JOIN pontaj_usr u ON u.user = p.user;

Quick checklist for reliable results and diagnostics: confirm data is a DATE and ora a TIME type (avoid text types that force casts), run EXPLAIN and EXPLAIN ANALYZE (if available) to see actual row counts, use ANALYZE TABLE to refresh index statistics, and test timing before/after each change. If queries are run repeatedly for reporting, consider a daily pre-aggregated summary table to keep SELECTs consistently fast.

Recommended Answers

All 3 Replies

Member Avatar for Szabi Zsoldos

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

Member Avatar for maniat123

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.