Query Speed Issue with NOT EXISTS condition

Question

I have a query that works, but it is slow. Is there a way to speed this up? Basically I have a table with timecard entries, and then a second table with time breakdowns of that entry, related by the TimecardID. What I am looking for is timeblocks that there are no breakdowns for. I thought if I cut the criteria down to 2 months that it would speed it up. Thanks for your help

SELECT * FROM Timecards
WHERE NOT EXISTS (SELECT TimeCardID FROM TimecardBreakdown WHERE Timecards.ID = TimecardBreakdown.TimeCardID) 
AND Status <> 0
AND DateIn >= CURRENT_DATE() - INTERVAL 2 MONTH

It's customary when asking for help optimizing a query to include the complete definitions of the tables involved. The output of `EXPLAIN` for the query also comes in handy. — O. Jones, Nov 30 '16 at 19:51
It has to do with your Database Management System and how your records are stored. With good indexing you can cut down on lookup times. So you would have to have access to the way the records are stored, if you do not, then you must actually look into your query as you mentioned and optimize it. But to optimize this, you should also look into your database tables and make sure they are normalized. If they are then you can move on to relational algebra and query optimization techniques. I can not help with your query because I don't know your tables so it is hard for me to re-write the query — Omid CompSCI, Nov 30 '16 at 19:51
I did some research after getting your reply and created a few indexes in my tables, and it made a huge difference. I had never done indexes in any of the databases that I have done before. Guess I have enough knowledge to get me in trouble, but not enough to be real good at it. Thank you for the point in the right direction. — user3338040, Dec 01 '16 at 00:28
@user3338040 that is awesome! I am glad you learned this and it is very important, hopefully it made a difference in your query speeds! Best of luck! — Omid CompSCI, Dec 02 '16 at 02:02

Abhinav Upadhyay · Accepted Answer · 2016-11-30T20:09:05.910

3

It seems you want to know the TimecardIDs which do not exist in the TimecardBreakdown table, in which case you can use the left outer join.

 SELECT a.*
 FROM Timecards a
 LEFT OUTER JOIN TimecardBreakdown b ON a.TimecardID = b.TimecardID
 WHERE b.TimecardID IS NULL

This would get rid of the subquery (which is expensive) and use join (which is more efficient).

edited Nov 30 '16 at 20:09

answered Nov 30 '16 at 19:59

Abhinav Upadhyay

2,477
20
32

I used your solution. Thanks so much for the help. Worked awesome. Super fast. – user3338040 Dec 04 '16 at 15:47
Glad it helped you, thanks for the upvote and accepting the answer. :-) – Abhinav Upadhyay Dec 04 '16 at 16:23

O. Jones · Answer 2 · 2016-12-01T12:33:55.660

MySQL stinks doing correlated subqueries fast. Try to make your subqueries independent and join them. You can use the LEFT JOIN ... IS NULL pattern to replace WHERE NOT EXISTS.

SELECT tc.*
  FROM Timecards tc
  LEFT JOIN TimecardBreakdown tcb ON tc.ID = tcb.TimeCardId
 WHERE tc.DateIn >= CURRENT_DATE() - INTERVAL 2 MONTH
   AND tc.Status <> 0
   AND tcb.TimeCardId IS NULL

Some optimization points.

First, if you can change tc.Status <> 0 to tc.Status > 0 it makes an index range scan possible on that column.

Second, when you're optimizing stuff, SELECT * is considered harmful. Instead, if you can give the names of just the columns you need, things will be quicker. The database server has to sling around all the data you ask for; it can't tell if you're going to ignore some of it.

Third, this query will be helped by a compound index on Timecards (DateIn, Status, ID). That compound index can be used to do the heavy lifing of satisfying your query conditions.

That's called a covering index; it contains the data needed to satisfy much of your query. If you were to index just the DateIn column, then the query handler would have to bounce back to the main table to find the values of Status and ID. When those columns appear in the index, it saves that extra operation.

If you SELECT a certain set of columns rather than doing SELECT *, including those columns in the covering index can dramatically improve query performance. That's one of several reasons SELECT * is considered harmful.

(Some makes and model of DBMS have ways to specify lists of columns to ride along on indexes without actually indexing them. MySQL requires you to index them. But covering indexes still help.)

Read this: http://use-the-index-luke.com/

Query Speed Issue with NOT EXISTS condition

2 Answers2