title: Mysql query optimisation foreign keys
body: <p>I have a master table and a child table with one foreign key. When I join between these two tables and use explain statement to analyse the performance </p>
  
  <ol>
  <li><p>I am getting the explain output as "Type=ALL" which is considered as worst for performance. How can I improve the performance of this join.</p></li>
  <li><p>explain output only shows "possible_keys: cid_index" instead of key and keylength</p></li>
  </ol>
  
  <p>Here is a test case</p>
  
  <pre><code> CREATE TABLE `master` (
    `mid` bigint(20) NOT NULL AUTO_INCREMENT,
    `mname` varchar(20) NOT NULL,
    PRIMARY KEY (`mid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
  
  
  select * from master;
  +-----+-------+
  | mid | mname |
  +-----+-------+
  |   1 | one   |
  |   2 | two   |
  +-----+-------+
  2 rows in set (0.25 sec)
  
  
   CREATE TABLE `child` (
    `cid` bigint(20) NOT NULL AUTO_INCREMENT,
    `cname` varchar(10) NOT NULL,
    `Ccid` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`cid`),
    KEY `cid_index` (`Ccid`),
    CONSTRAINT `new_fk_constraint` FOREIGN KEY (`Ccid`) REFERENCES `master` (`mid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
  
  
  
   select * from child;
  +-----+-------+------+
  | cid | cname | Ccid |
  +-----+-------+------+
  |   1 | Cone  |    1 |
  |   2 | ctwo  |    2 |
  +-----+-------+------+
  2 rows in set (0.12 sec)
  
  
  
  explain select m.*,c.* from master m join child c on  m.mid=c.Ccid \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: m
           type: ALL
  possible_keys: PRIMARY
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 2
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: c
           type: ALL
  possible_keys: cid_index
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 2
          Extra: Using where; Using join buffer
  2 rows in set (0.23 sec)
  </code></pre>