title: Composite index not used in mysql
body: <p>According to MySQL docs a composite index will still be used if the leftmost fields are part of the criteria.  However, this table will not join correctly with the primary key; I had to add another index of the left two fields which is then used.</p>
  
  <p>One of the tables is memory, and I know that by default memory uses a hash index which can't be used for group/order.  However I'm using all rows of the memory table and not the index, so I don't think that relates to the problem.</p>
  
  <p>What am I missing?</p>
  
  <pre><code>mysql&gt; show create table pr_temp;
  | pr_temp | CREATE TEMPORARY TABLE `pr_temp` (
  `player_id` int(10) unsigned NOT NULL,
  `insert_date` date NOT NULL,
  [...]
  PRIMARY KEY (`player_id`,`insert_date`) USING BTREE,
  KEY `insert_date` (`insert_date`)
  ) ENGINE=MEMORY DEFAULT CHARSET=utf8  |
  
  mysql&gt; show create table player_game_record;
  | player_tank_record | CREATE TABLE `player_game_record` (
  `player_id` int(10) unsigned NOT NULL,
  `game_id` smallint(5) unsigned NOT NULL,
  `insert_date` date NOT NULL,
  [...]
  PRIMARY KEY (`player_id`,`insert_date`,`game_id`),
  KEY `insert_date` (`insert_date`),
  KEY `player_date` (`player_id`,`insert_date`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 DATA DIRECTORY='...' INDEX DIRECTORY='...' |
  
  mysql&gt; explain select pgr.* from player_game_record pgr inner join pr_temp on   pgr.player_id = pr_temp.player_id and pgr.insert_date = pr_temp.date_prev;                      
  +----+-------------+---------+------+---------------------------------+-------------+---------+-------------------------------------------------------------------------+--------+-------+
  | id | select_type | table   | type | possible_keys                   | key         | key_len | ref                                                                     | rows       | Extra |
  +----+-------------+---------+------+---------------------------------+-------------+---------+-------------------------------------------------------------------------+--------+-------+
  |  1 | SIMPLE      | pr_temp | ALL  | PRIMARY                         | NULL        | NULL    | NULL                                                                    | 174683   |       |
  |  1 | SIMPLE      | pgr     | ref  | PRIMARY,insert_date,player_date | player_date | 7       | test_gamedb.pr_temp.player_id,test_gamedb.pr_temp.date_prev |     21 |       |
  +----+-------------+---------+------+---------------------------------+-------------+---------+-------------------------------------------------------------------------+--------+-------+
  2 rows in set (0.00 sec)
  
  mysql&gt; explain select pgr.* from player_game_record pgr force index (primary) inner join pr_temp on pgr.player_id = pr_temp.player_id and pgr.insert_date = pr_temp.date_prev;
  +----+-------------+---------+------+---------------+---------+---------+-------------------------------------------------------------------------+---------+-------+
  | id | select_type | table   | type | possible_keys | key     | key_len | ref                                                                       | rows    | Extra |
  +----+-------------+---------+------+---------------+---------+---------+-------------------------------------------------------------------------+---------+-------+
  |  1 | SIMPLE      | pr_temp | ALL  | PRIMARY       | NULL    | NULL    | NULL                                                                    |  174683 |       |
  |  1 | SIMPLE      | pgr     | ref  | PRIMARY       | PRIMARY | 7       | test_gamedb.pr_temp.player_id,test_gamedb.pr_temp.date_prev | 2873031 |       |
  +----+-------------+---------+------+---------------+---------+---------+-------------------------------------------------------------------------+---------+-------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>I think the primary key should work, with the two left columns (player_id, insert_date) being used.  However it will use the player_date index by default, and if I force it to use the primary index it looks like it's only using one field rather than both.</p>
  
  <p>Update2: Mysql version 5.5.27-log
  Update3:
  (note this is after removing the player_date index while trying some other tests)</p>
  
  <pre><code>mysql&gt; show indexes in player_game_record;
  +--------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table              | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | player_game_record |          0 | PRIMARY     |            1 | player_id   | A            |        NULL |     NULL | NULL   |      | BTREE      |         |               |
  | player_game_record |          0 | PRIMARY     |            2 | insert_date | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
  | player_game_record |          0 | PRIMARY     |            3 | game_id     | A         |   576276246 |     NULL | NULL   |      | BTREE      |         |               |
  | player_game_record |          1 | insert_date |            1 | insert_date | A         |       33304 |     NULL | NULL   |      | BTREE      |         |               |
  +--------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  4 rows in set (1.08 sec)
  
  
  mysql&gt; select count(*) from player_game_record;
  +-----------+
  | count(*)  |
  +-----------+
  | 576276246 |
  +-----------+
  1 row in set (0.00 sec)
  </code></pre>