sql - MySQL index not being used as expected -

what can make mysql use expected indices?

i've got 4 tables, 2 containing resources, , others containing historical changes.

one pair uses indexes correctly, other doesn't, both structured identically.

i've tried changing order of primary keys, , order of other keys, i've tried changing table structures use same name in both tables, , both have same key names, nothing seems make query use correct index.

irrelevant columns have been removed brevity.

these 2 tables working expected.

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) pop_rank_score fpme_lua.`players` p, fpme_lua.poprankhistory h1 ( p.`player_id` = h1.`player_id` , h1.day_id = (select max(h2.day_id) day_id     fpme_lua.poprankhistory h2    h2.day_id <= 15786 , h2.player_id = p.`player_id` ));  +----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+ | id | select_type        | table | type   | possible_keys            | key       | key_len | ref                   | rows  |                    | +----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+ |  1 | primary            | h1    |    | primary,day_id,player_id | null      | null    | null                  | 25391 |                          | |  1 | primary            | p     | eq_ref | primary                  | primary   | 38      | fpme_lua.h1.player_id |     1 | using              | |  2 | dependent subquery | h2    | ref    | primary,day_id,player_id | player_id | 38      | fpme_lua.p.player_id  |     2 | using where; using index | +----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+ 

these tables aren't working expected (required).

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) pcr_score fpme_lua.`pictures` p, fpme_lua.picpcrhistory h1 ( p.`id` = h1.`target_id` , h1.day_id = (select max(h2.day_id) day_id     fpme_lua.picpcrhistory h2    h2.day_id <= 15786 , h2.`target_id` = p.`id` ));  +----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+ | id | select_type        | table | type   | possible_keys  | key     | key_len | ref  | rows  |                    | +----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+ |  1 | primary            | h1    |    | primary,day_id | null    | null    | null | 65310 |                          | |  1 | primary            | p     | eq_ref | primary        | primary | 110     | func |     1 | using              | |  2 | dependent subquery | h2    | range  | primary,day_id | day_id  | 4       | null | 21824 | using where; using index | +----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+ 

the tables had different character sets, , thats why index didn't work. changed charset both utf8 , added index 3 columns.

alter table `fpme_lua`.`colpcrhistory` character set = utf8 ; alter table `fpme_lua`.`picpcrhistory` character set = utf8 ; alter table `fpme_lua`.`picpcrhistory`  add index `indx_tar_day_tot` using btree (`target_id` asc, `day_id` asc, `total` asc) ; 

then changed query to...

select p.id,         p.pcr_score + 0.5 * coalesce(h1.total,0) pcr_score      fpme_lua.pictures p   join      fpme_lua.picpcrhistory h1       on  h1.target_id = p.id    join     ( select   hh.target_id,                max(hh.day_id) day_id           fpme_lua.picpcrhistory hh          hh.day_id <= 15786        group hh.target_id     ) h2       on  h2.target_id = h1.target_id       , h2.day_id = h1.day_id ;