MySQL index not used as expected

Question

What can I do to make mysql use the expected indexes?

I have four tables, two of which contain resources, and the rest contain historical changes.

One pair uses indexes correctly, the other does not, but both are structured almost identically.

I tried changing the order of the primary keys and the order of the other keys, I tried changing the structure of the tables so that they use the same name in both tables, and both have the same key names, but nothing seems like the query is using the correct index.

For brevity, unnecessary columns have been removed.

These two tables work as 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) 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 <= 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 | +----+--------------------+-------+--------+--------------------------+-----------+---------+-----------------------+-------+--------------------------+ 

These tables do not work as 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) 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 <= 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 | +----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+ 

Answer 1

The tables had different character sets, so the index did not work. I changed charset so that both of them were utf8 and added an index for three 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 I changed the request to ...

 SELECT p.id, p.pcr_score + 0.5 * COALESCE(h1.total,0) AS pcr_score FROM fpme_lua.Pictures AS p JOIN fpme_lua.PicPcrHistory AS h1 ON h1.target_id = p.id JOIN ( SELECT hh.target_id, Max(hh.day_id) AS day_id FROM fpme_lua.PicPcrHistory AS hh WHERE hh.day_id <= 15786 GROUP BY hh.target_id ) AS h2 ON h2.target_id = h1.target_id AND h2.day_id = h1.day_id ; 

Answer 2

If you want to use a specific index, you can use FORCE INDEX(index_name) after the table name. Although MySQL should be smart enough to choose the "best" index.