Mysql don't use index while self joining a table with indexed criteria

Question

When self joining a table with criteria as indexed column, mysql goes for full table scan. By changing the join order , we can able to use index .

Table schema for EmployeeDetails

EmployeeDetails | CREATE TABLE `EmployeeDetails` (
  `CompanyId` bigint(19) NOT NULL DEFAULT '0',
  `EmployeeId` bigint(19) NOT NULL DEFAULT '0',
  `ParentEmployeeId` bigint(19) DEFAULT NULL,
  PRIMARY KEY (`EmployeeId`),
  KEY `EmployeeDetails_FK1_IDX` (`CompanyId`),
  KEY `EmployeeDetails_FK2_IDX` (`ParentEmployeeId`),
  CONSTRAINT `EmployeeDetails_FK1` FOREIGN KEY (`CompanyId`) REFERENCES `CompanyDetails` (`CompanyId`) ON DELETE CASCADE,
  CONSTRAINT `EmployeeDetails_FK2` FOREIGN KEY (`ParentEmployeeId`) REFERENCES `EmployeeDetails` (`EmployeeId`) ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Table schema for CompanyDetails

| CompanyDetails | CREATE TABLE `CompanyDetails` (
  `CompanyId` bigint(19) NOT NULL DEFAULT '0',
  `NAME` varchar(25) NOT NULL DEFAULT '',
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`CompanyId`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
|

First query - first select will scan all records in EmployeeDetails table and do self joining (won't use index KEY EmployeeDetails_FK1_IDX (CompanyId) which is used in criteria)

mysql> select * from EmployeeDetails INNER JOIN `EmployeeDetails` `t1` ON `EmployeeDetails`.`EmployeeId`=`t1`.`ParentEmployeeId` where `EmployeeDetails`.`CompanyId` IN(123);
Empty set (0.10 sec)

mysql> explain select * from EmployeeDetails INNER JOIN `EmployeeDetails` `t1` ON `EmployeeDetails`.`EmployeeId`=`t1`.`ParentEmployeeId` where `EmployeeDetails`.`CompanyId` IN(123)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: EmployeeDetails_FK2_IDX
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7999
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: EmployeeDetails
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,EmployeeDetails_FK1_IDX
          key: PRIMARY
      key_len: 8
          ref: db.t1.ParentEmployeeId
         rows: 1
     filtered: 5.00
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

Second query - first select records from EmployeeDetails using EmployeeDetails_FK1_IDX(CompanyId`) index and scan only records matching the criteria and do self joining

mysql> select * from EmployeeDetails INNER JOIN `EmployeeDetails` `t1` ON `t1`.`EmployeeId`=`EmployeeDetails`.`ParentEmployeeId` where `EmployeeDetails`.`CompanyId` IN(123);
Empty set (0.00 sec)

mysql> explain select * from EmployeeDetails INNER JOIN `EmployeeDetails` `t1` ON `t1`.`EmployeeId`=`EmployeeDetails`.`ParentEmployeeId` where `EmployeeDetails`.`CompanyId` IN(123)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: EmployeeDetails
   partitions: NULL
         type: ref
possible_keys: EmployeeDetails_FK1_IDX,EmployeeDetails_FK2_IDX
          key: EmployeeDetails_FK1_IDX
      key_len: 8
          ref: const
         rows: 54
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: db.EmployeeDetails.ParentEmployeeId
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

Why this happens?

Answer 1

As it can be seen in the EXPLAIN, "EmployeeDetails_FK2_IDX" is listed as a possible key. If MySQL optimizer doesn't use it, it's just that it would not reduce the query time.

Try to populate table with data (I checked it and it works as is) and you should see that MySQL optimizer will use an index for the join without looking their orders. Because orders of joins do not have any affect on MySQL optimizer algorithms.

Also check STRAIGHT_JOIN