Why Mysql does not use index on INNER JOIN tables with index column?

Question

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?

Answer 1

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.