Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
+----+--------------------+-------+--------+----------------+---------+---------+------+-------+--------------------------+
share|improve this question

2 Answers 2

up vote 1 down vote accepted

I think that better indexes for these queries are these (which you don't have on the tables now): (player_id, day_id) and (target_id, day_id)

The optimizer choosing different indexes in the two situations has probably to due with table sizes and selectivity of the available indexes. I suggest you add the two indexes above and check (and compare) the new execution plans and timings with the old ones.

Update For InnoDB tables, the (target_id) and (target_id, day_id) indexes should be equivalent as all non clustered indexes also include the columns of the clustered index. It could be a blind spot on the optimizer, not identifying that the (target_id) index can be used as if it was a (target_id, day_id) index. But seeing that the similar index in the first case is chosen, the problem must be somewhere else.

And it is: PicPcrHistory.target_id is of type varchar(36) and so is Pictures.id But one has utf8 and the other latin1 charset. So, there is your problem and why the index is not used. The two columns that are joined are not identical (type and charset.)

Change the charset of either one to match the other charset and the index will be used.


There are also other ways to write the queries - which appear to be of the [greatest-n-per-group] type:

  • Using a derived table:

    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 ;
    
  • Using a correlated subquery for the join condition (this is similar to the queries you have):

    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 
          AND h1.day_id =
            ( SELECT   hh.day_id
              FROM     fpme_lua.PicPcrHistory AS hh
              WHERE    hh.target_id = p.id
                AND    hh.day_id <= 15786 
              ORDER BY hh.target_id DESC
                  LIMIT 1
            ) ;
    

And index on (target_id, day_id, total) will be even better for the last query.

share|improve this answer
    
Thanks, it worked using derived table and new index on (target_id, day_id, total) –  Jason Pascoe May 3 '13 at 11:12
    
Don't forget to match the charsets (backup first, and test with copies of the tables so no data is lost of course). It's easier to do now that the tables are small. –  ypercube May 3 '13 at 11:24
    
All my testing and updates are done locally first, and we have three server environments, dev, test and production. Unfortunately one of the history tables already has over 1.7 million rows and won't let me add the new index. The reason for the new query was to change the history table to sparse updates. I'm creating a new table locally at the moment with the changes, then I'll copy all the rows from the old table to the new one. –  Jason Pascoe May 3 '13 at 12:23

You can use the index hints: USE, IGNORE and FORCE.

Example:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;

Explanation from the manual:

By specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) can be used to tell MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

You can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.