4

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

I've got 4 tables, two containing resources, and the others containing historical changes.

One pair uses indexes correctly, the other doesn't, but both are structured almost identically.

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.

Irrelevant columns have been removed for brevity.

These two tables are working 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 aren't working 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 |
+----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+
Jason Pascoe
  • 337
  • 2
  • 12
  • The first query includes the pk, and both indexes as possible_keys, but the second query, doesn't include target_id, which is the one I want to use even though I've specified the two keys in the where clause. – Jason Pascoe May 03 '13 at 02:53

2 Answers2

2

The tables had different character sets, and thats why the index didn't work. I changed the charset so both were utf8 and added an index for the 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 query 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 ;
Jason Pascoe
  • 337
  • 2
  • 12
  • Ahh, nice catch, thanks for documenting it. (Well, you haven't actually accepted your answer, so we still can't be 100% sure, though. ;) ) – Sz. Apr 05 '14 at 17:52
1

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 select the "best" index.

MISJHA
  • 998
  • 4
  • 12