2

First of all i have the following table structures.

Table Document
     ## DocID ## ##DocName ##
         1          Doc1
         2          Doc2
         3          Doc3 
Table FolderTree
     ## FolderID ##  ## MemberDocID ##
          1                1
          1                2
          1                3

I have index on DocID, FolderID and MemberDocID

I have the following query.

 SELECT DISTINCT d.* FROM Document d inner join FolderTree f on (d.DocID = f.MemberDocID ) where f.FolderID = 1

Explain Output:

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

   simple        d     All     PRIMARY        NULL          83168    Using temporary
   simple        f     ref     MemberDocID    MemberDocID   11       Using index

My question is, why mysql use table scan on table d where i have index on DocID?

Wenfang Du
  • 8,804
  • 9
  • 59
  • 90
Ellie Fabrero
  • 791
  • 2
  • 16
  • 41
  • @bernie The root cause here is different than that in the linked possible duplicate. Though they sounds like similar issues, they are not. – Mike Brant Feb 22 '13 at 21:46

1 Answers1

2

It is because you are selecting DISTINCT on all columns in Document table. There is no index on DocName, so it can not optimize the search for distinct values.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • I removed the DISTINCT KEYWORD and added index on DocName. It is using index as key right now but the rows to be examined has the same value. Is there any way i can further optimize my query or is there anything i need to add for me to best optimize my query. thanks – Ellie Fabrero Feb 22 '13 at 22:01
  • You don't need the index on DocName if you are not querying for DISTINCT. How many rows are in the Document table? You might actually get better performance by swapping the order of the tables in your join since you are filtering on a column from the folderTree table. As such this should probably be the table you have MySQL read first. – Mike Brant Feb 22 '13 at 22:17