title: How to tune self-join table in mysql like this?
body: <p>I have this table which I'm trying to select from and to date. The query took 2 min to run on 4 million records. I'm not sure how much more I can squeeze out of this query. </p>
  
  <pre><code>    SELECT c.fk_id, c.from_date, c.fk_pb, MIN(o.from_date) AS to_date  
      FROM TABLE_X c  
          INNER JOIN TABLE_X o ON c.fk_id =  o.fk_id AND c.fk_pb = o.fk_pb  
  WHERE o.from_date &gt; c.from_date  
          GROUP BY c.fk_id, c.from_date, c.fk_pb
  </code></pre>
  
  <p>There are indexes on from_date, fk_pb and fk_id already. </p>
  
  <p>The schema is like this.</p>
  
  <pre><code>+-----------------------------+---------------+------+-----+---------+-------+
  | Field                       | Type          | Null | Key | Default | Extra |
  +-----------------------------+---------------+------+-----+---------+-------+
  | FK_ID                       | int(11)       | YES  | MUL | NULL    |       |
  | FK_PB                       | int(11)       | YES  | MUL | NULL    |       |
  | FROM_DATE                   | date          | YES  | MUL | NULL    |       |
  | TO_DATE                     | date          | YES  |     | NULL    |       |
  +-----------------------------+---------------+------+-----+---------+-------+
  </code></pre>
  
  <p>I know I should not use self-join at all in MySQL, but the data comes like this and I'm trying to find the best way to select from and to date out of this table. If there's anything else I could do to make this one faster that'd be great.</p>
  
  <p>Thanks a lot.</p>
  
  <p>UPDATED</p>
  
  <pre><code>+----+-------------+-------+------+----------------------------------------------------------------------+-------------------------+---------+----------------------------------------+---------+----------------------------------------------+
  | id | select_type | table | type | possible_keys                                                        | key                     | key_len | ref                                    | rows    | Extra                                        |
  +----+-------------+-------+------+----------------------------------------------------------------------+-------------------------+---------+----------------------------------------+---------+----------------------------------------------+
  |  1 | SIMPLE      | c     | ALL  | IDX_FK_PB,IDX_FK_ID,IDX_FRM_DATE                                     | NULL                    | NULL    | NULL                                   | 4527750 | Using where; Using temporary; Using filesort |
  |  1 | SIMPLE      | o     | ref  | IDX_FK_PB,IDX_FK_ID,IDX_FRM_DATE                                     | IDX_FK_ID               | 5       | db.c.FK_ID                             |     110 | Using where                                  |
  +----+-------------+-------+------+----------------------------------------------------------------------+-------------------------+---------+----------------------------------------+---------+----------------------------------------------+
  </code></pre>