CODEWITHSUNDEEP
mysqlsqldatabase
Ellie Fabrero
Ellie Fabrero

Reputation: 811

Why Mysql dont used index on WHERE clause using OR?

First of all i have the following table structures.

Table Document
  ## DocID ##  ## DocName## ## DuplicateID ##
      1              Doc1        null
      2              Doc2        null
      3              Doc3        null
      4              Doc4         1

Table FolderTree
  ## FolderID ##  ## MemberDocID ##
       1                1
       1                2
       1                3

I have index on DocID, DuplicateID and MemberDocID and FolderID

My Query is this : 

SELECT d.* 
from Document d, FolderTree f 
WHERE (d.DocID = f.MemberDocID or d.DuplicateID = f.MemberDocID) and f.FolderID = 1 
GROUP BY d.DocID ;

So basically i want to retrieve all documents from Folder with id 1 and also its duplicate documents from the table. The group by is used to maintain uniqueness of record that no document will be retrieve twice.

This query is working fine but in large volume of records it getting slower.Here is the explain output.

| select type | table | type | possible_keys | key    | rows  |   extra         |

   simple        d     range   PRIMARY,...    PRIMARY   83168   Using temporary..
   simple        f     All     DuplicateIDInx Null      108787  Using join buffer

What concerns me is that the table f does not use the index on DuplicateID. My question is, Why is it so? Can somebody enlighten me on this matter. Im using Mysql 5.x Thanks :)

Upvotes: 3

Views: 617

Answers (3)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

You can use an in clause instead and use distinct instead of group by.

SELECT distinct d.* 
from Document d
join FolderTree f on f.MemberDocID in (d.DocID, d.DuplicateID)
WHERE f.FolderID = 1

According to SQL Fiddle, this hasn't changed much, besides making the join explicit.

If you change your duplicateid, however

update document
set duplicateid = docid
where duplicateid is null

and select on duplicateid only 

SELECT distinct d.* 
from Document d
join FolderTree f on f.MemberDocID = d.DuplicateID
WHERE f.FolderID = 1

will use the index on duplicateid

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Try this version:

SELECT d.* 
FROM Document d 
WHERE EXISTS
      ( SELECT *
        FROM FolderTree f 
        WHERE ( d.DocID = f.MemberDocID OR d.DuplicateID = f.MemberDocID )
          AND f.FolderID = 1 
      ) ;

and this one (edited):

SELECT d.* 
FROM ( SELECT 1 AS FolderID
     ) AS Parameter
  CROSS JOIN 
     Document d 
WHERE EXISTS
      ( SELECT *
        FROM FolderTree f 
        WHERE f.MemberDocID = d.DocID
          AND f.FolderID = Parameter.FolderID
      ) 
   OR EXISTS
      ( SELECT *
        FROM FolderTree f 
        WHERE f.MemberDocID = d.DuplicateID
          AND f.FolderID = Parameter.FolderID
      ) ;

I would also add a compound (composite) index on FolderTree (FolderID, MemberDocID).

If you haven't already an index on Document (DuplicateID), add one as well.

The extra requirement could probably be better solved by writing the query in a stored procedure with a parameter.

Upvotes: 1

dkkumargoyal
dkkumargoyal

Reputation: 556

looks like proper indexes are not there. can you clarify more on the table structure and query.

  1. about f.MemberID column used in query at "or d.DuplicateID = f.MemberID"
  2. explain shows DuplicateIDInx index name in table FolderTree. can you mention exact index defination from tables.

Upvotes: 1

Related Questions