CODEWITHSUNDEEP
mysqlsqljoinquery-optimizationin-subquery
user2363025
user2363025

Reputation: 6515

optimise IN subquery

I have a workers table and an associated workerGeofence table.

CREATE TABLE IF NOT EXISTS `workergeofences` (
`ID` int(11) NOT NULL,
  `WorkerID` varchar(20) NOT NULL,
  `GeofenceID` int(11) NOT NULL,
  `isActive` tinyint(4) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=latin1;

I need to return only workers who have at least one entry in the workerGeofences table with an isActive of 1.

I'm able to get the desired outcome withe following:

    SELECT distinct w.ID, Title, FName, SName, Email, Birthday, Address, Phone, description,
 companyID 
FROM Workers w WHERE companyID = ? 
and w.ID IN (SELECT WorkerID FROM WorkerGeofences WHERE isActive <> 0)
    limit ?,10

but the in subquery is exhaustive as when I run the explain, I can see it is scanning the entire table. How would I get around this?

Upvotes: 0

Views: 59

Answers (3)

sagi
sagi

Reputation: 40491

First of all , your join is wrong! you are not comparing any common column on both table, you should add where workerGeofences.workerID = w.id like this:

SELECT  w.ID, Title, FName, SName, Email, Birthday, Address, Phone,
        description, companyID
    FROM  Workers w
    join  workerGeofences
    WHERE  workerGeofences.workerID = w.ID companyID = ?
      and  w.ID IN (
        SELECT  WorkerID
            FROM  WorkerGeofences s
            WHERE  isActive <> 0
              and  s.workerID = w.id
                   )
    limit  0,10

And second, you are not selecting anything from the second table, so the join is unessesary and in your IN statement, you are not comparing the right ID's so your query should be:

SELECT  w.ID, Title, FName, SName, Email, Birthday, Address, Phone,
        description, companyID
    FROM  Workers w
    WHERE  companyID = ?
      and  w.ID IN (
        SELECT  WorkerID
            FROM  WorkerGeofences s
            WHERE  isActive <> 0
              and  s.workerID = w.ID
                   )
    limit  0,10

Also, you can use EXISTS() for that.

SELECT  w.ID, Title, FName, SName, Email, Birthday, Address, Phone,
        description, companyID
    FROM  Workers w
    WHERE  companyID = ?
      and  exists 
      ( SELECT  1
            FROM  WorkerGeofences s
            WHERE  isActive = 1
              and  s.workerID = w.ID
      )
    limit  0,10

Upvotes: 1

Arth
Arth

Reputation: 13110

For completeness using JOIN:

SELECT DISTINCT w.ID,
       w.Title, 
       w.FName, 
       w.SName, 
       w.Email, 
       w.Birthday, 
       w.Address, 
       w.Phone, 
       w.description, 
       w.companyID
  FROM Workers w 
  JOIN WorkerGeofences wg
    ON wg.workerID = w.id
   AND wg.isActive = 1
 WHERE w.companyID = ? 
 LIMIT ?,10

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You are on the right track, but you shouldn't need select distinct. This slows down queries, unless you know there are duplicates -- and that is unlikely because you are selecting WOrkers.Id.

SELECT w.* 
FROM Workers w 
WHERE w.companyID = ? AND
      EXISTS (SELECT 1
              FROM workerGeofences wg
              WHERE w.ID = wg.WorkerID AND wg.isActive <> 0
             )
LIMIT ?, 10;

Then, for this query, you want indexes on Workers(CompanyId, Id) and workerGeofences(WorkerId, isActive).

Note: I just put in select * for convenience. I assume all the columns are coming from the Workers table.

Upvotes: 2

Related Questions