CODEWITHSUNDEEP
mysqlquery-optimization
Nosajimiki
Nosajimiki

Reputation: 1103

How to optimize a MySQL select with rows that do not have matching values in the other table

This question is more or less the same as this one: MySQL select rows that do not have matching column in other table; however, the solution there is not not practical for large data sets.

This table has ~120,000 rows. 

CREATE TABLE `tblTimers` (
  `TimerID` int(11) NOT NULL,
  `TaskID` int(11) NOT NULL,
  `UserID` int(11) NOT NULL,
  `StartDateTime` datetime NOT NULL,
  `dtStopTime` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `tblTimers`
  ADD PRIMARY KEY (`TimerID`);
ALTER TABLE `tblTimers`
  MODIFY `TimerID` int(11) NOT NULL AUTO_INCREMENT;

This table has about ~70,000 rows.

CREATE TABLE `tblWorkDays` (
  `WorkDayID` int(11) NOT NULL,
  `TaskID` int(11) NOT NULL,
  `UserID` int(11) NOT NULL,
  `WorkDayDate` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `tblWorkDays`
  ADD PRIMARY KEY (`WorkDayID`);

ALTER TABLE `tblWorkDays`
  MODIFY `WorkDayID` int(11) NOT NULL AUTO_INCREMENT;

tblWorkDays should have one line per TaskID per UserID per WorkDayDate, but due to a bug, a few work days are missing despite there being timers for those days; so, I am trying to create a report that shows any timer that does not have a work day associated with it.

SELECT A.TimerID FROM tblTimers A
LEFT JOIN tblWorkDays B ON A.TaskID = B.TaskID AND A.UserID = B.UserID AND DATE(A.StartDateTime) = B.WorkDayDate
WHERE B.WorkDayID IS NULL

Doing this causes the server to time out; so, I am looking for if there is a way to do this more efficiently?

Upvotes: 1

Views: 52

Answers (1)

Barmar
Barmar

Reputation: 781255

You don't have any indexes on the columns you're joining on, so it has to do full scans of both tables. Try adding the following:

ALTER TABLE tblTimers ADD INDEX (TaskID, UserID);
ALTER TABLE tblWorkDays ADD INDEX (TaskID, UserID);

Upvotes: 2

Related Questions