title: MySQL index not being used as expected
body: <h1>What can I do to make mysql use the expected indices?</h1>
  
  <p>I've got 4 tables, two containing resources, and the others containing historical changes.</p>
  
  <p>One pair uses indexes correctly, the other doesn't, but both are structured almost identically.</p>
  
  <p>I've tried changing the order of the primary keys, and the order of the other keys, I've tried changing table structures so they use the same name in both tables, and both have the same key names, but nothing seems to make the query use the correct index.</p>
  
  <p><em>Irrelevant columns have been removed for brevity.</em></p>
  
  <h3>These two tables are working as expected.</h3>
  
  <pre><code>CREATE TABLE `players` (
    `player_id` varbinary(36) NOT NULL DEFAULT '',
    `pop_rank_score` double NOT NULL DEFAULT '0',
    PRIMARY KEY (`player_id`),
    KEY `pop_rank_score` (`pop_rank_score`),
    KEY `weblinc_id` (`weblinc_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  
  CREATE TABLE `poprankhistory` (
    `day_id` int(11) NOT NULL,
    `player_id` varbinary(36) NOT NULL DEFAULT '',
    `total` double NOT NULL DEFAULT '0',
    `today` double NOT NULL DEFAULT '0',
    PRIMARY KEY (`day_id`,`player_id`),
    KEY `day_id` (`day_id`),
    KEY `player_id` (`player_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  
  
  explain select p.`player_id`, p.pop_rank_score + 0.5 * COALESCE(h1.total,0) as pop_rank_score
  from fpme_lua.`Players` p, fpme_lua.PopRankHistory h1
  where ( p.`player_id` = h1.`player_id` AND h1.day_id = (SELECT Max(h2.day_id) AS day_id
    FROM   fpme_lua.poprankhistory h2
    WHERE  h2.day_id &lt;= 15786 and h2.player_id = p.`player_id` ));
  
  +----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+
  | id | select_type        | table | type   | possible_keys            | key       | key_len | ref                   | rows  | Extra                    |
  +----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+
  |  1 | PRIMARY            | h1    | ALL    | PRIMARY,day_id,player_id | NULL      | NULL    | NULL                  | 25391 |                          |
  |  1 | PRIMARY            | p     | eq_ref | PRIMARY                  | PRIMARY   | 38      | fpme_lua.h1.player_id |     1 | Using where              |
  |  2 | DEPENDENT SUBQUERY | h2    | ref    | PRIMARY,day_id,player_id | player_id | 38      | fpme_lua.p.player_id  |     2 | Using where; Using index |
  +----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+
  </code></pre>
  
  <h3>These tables aren't working as expected (required).</h3>
  
  <pre><code>CREATE TABLE `pictures` (
    `id` varchar(36) NOT NULL DEFAULT '',
    `pcr_score` double NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `owner_id` (`owner_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  
  CREATE TABLE `picpcrhistory` (
    `day_id` int(11) NOT NULL,
    `target_id` varchar(36) NOT NULL DEFAULT '',
    `total` double NOT NULL DEFAULT '0',
    `today` double NOT NULL DEFAULT '0',
    PRIMARY KEY (`day_id`,`target_id`),
    KEY `target_id` (`target_id`),
    KEY `day_id` (`day_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  
  
  explain select p.`id`, p.pcr_score + 0.5 * COALESCE(h1.total,0) as pcr_score
  from fpme_lua.`Pictures` p, fpme_lua.PicPcrHistory h1
  where ( p.`id` = h1.`target_id` AND h1.day_id = (SELECT Max(h2.day_id) AS day_id
    FROM   fpme_lua.PicPcrHistory h2
    WHERE  h2.day_id &lt;= 15786 and h2.`target_id` = p.`id` ));
  
  +----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+
  | id | select_type        | table | type   | possible_keys  | key     | key_len | ref  | rows  | Extra                    |
  +----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+
  |  1 | PRIMARY            | h1    | ALL    | PRIMARY,day_id | NULL    | NULL    | NULL | 65310 |                          |
  |  1 | PRIMARY            | p     | eq_ref | PRIMARY        | PRIMARY | 110     | func |     1 | Using where              |
  |  2 | DEPENDENT SUBQUERY | h2    | range  | PRIMARY,day_id | day_id  | 4       | NULL | 21824 | Using where; Using index |
  +----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+
  </code></pre>