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.

Answer 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;