Not using index key on using 'OR'

Question

I have a query like that :

SELECT * FROM Backlog b
WHERE b.projectid=10
AND EXISTS (SELECT 1 FROM Requirement req 
        WHERE req.projectid=10 
        AND req.status='Approved'
        AND req.deleted!=TRUE 
        AND req.backlog=b.id 
        AND b.majorrelease IS NULL 
        AND b.iteration IS NULL ) 
OR EXISTS(SELECT 1 FROM Defect def 
        WHERE def.projectid=10
        AND def.status!='Open' 
        AND def.deleted!=TRUE 
        AND def.backlog=b.id 
        AND b.majorrelease IS NULL 
        AND b.iteration IS NULL ) 
ORDER BY b.rank ASC,b.priority ASC;

and its explain plan is -

id  select_type         table  type  possible_keys                             key                key_len  ref            rows  Extra                        
1   PRIMARY             b      ALL   projectid                                                                            4258  Using where; Using filesort  
3   DEPENDENT SUBQUERY  def    ref   defect_backlog_fk,defect_bl_fk,projectid  defect_backlog_fk  8        eyeagile.b.id  1     Using where                  
2   DEPENDENT SUBQUERY  req    ref   req_backlog_FK,projectid                  req_backlog_FK     9        eyeagile.b.id  1     Using where

On using 'OR' its not using index on projectid. I have tried force index on it but also not using index. Is there something wrong? Please let me know thank you.

Hawk · Accepted Answer · 2013-12-27 11:13:02Z

1

Try this:

SELECT * 
FROM   backlog b 
       LEFT JOIN requirement req 
              ON req.backlog = b.id 
                 AND req.projectid = 10 
                 AND req.status = 'Approved' 
                 AND req.deleted != true 
                 AND b.majorrelease IS NULL 
                 AND b.iteration IS NULL 
       LEFT JOIN defect def 
              ON def.backlog = b.id 
                 AND def.projectid = 10 
                 AND def.status != 'Open' 
                 AND def.deleted != true 
                 AND b.majorrelease IS NULL 
                 AND b.iteration IS NULL 
WHERE  b.projectid = 10 
       AND ( req.reqid IS NOT NULL 
              OR def.defid IS NOT NULL ) 
ORDER  BY b.rank ASC, 
          b.priority ASC;

edited Dec 27, 2013 at 11:13

Hawk

5,17014 gold badges55 silver badges78 bronze badges

answered Dec 27, 2013 at 7:02

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aamir Over a year ago

thank you for the reply, but condition >AND (req.reqId IS NOT NULL OR def.defId IS NOT NULL) How i can use this in where??

Saharsh Shah Over a year ago

@Aamir0731 Just use primary key of that table. In my answer I assume that reqId is the primary key of Requirement table. So replace the column name with actual name. Do the same for Defect table.

Aamir Over a year ago

its correct, I have done the same but explain plan is same its not using index again.

Collectives™ on Stack Overflow

Not using index key on using 'OR'

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related