my mysql index is not having any effect

Question

I have the following query:

mysql> explain SELECT Exhibition.venue_id, Exhibition.name, Exhibition.slug, Venue.name, Venue.slug, Venue.location_id, Venue.id, Exhibition.id FROM exhibitions AS Exhibition LEFT JOIN venues AS Venue ON (Exhibition.venue_id = Venue.id) LEFT JOIN temperatures AS Temperature ON (Temperature.ref = Exhibition.id) WHERE Exhibition.active = '1' AND Exhibition.ends <= CURDATE() ORDER BY Temperature.temperature DESC LIMIT 5;
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Exhibition  | ALL    | NULL          | NULL    | NULL    | NULL                     | 1530 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Venue       | eq_ref | PRIMARY       | PRIMARY | 108     | narb.Exhibition.venue_id |    1 |                                              | 
|  1 | SIMPLE      | Temperature | ALL    | NULL          | NULL    | NULL    | NULL                     | 2649 |                                              | 
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

Now since there is an index on (Exhibition.venue_id, Exhibition.active, Exhibition.ends) and on Temperature.ref i would expect the first and last join to be able to use the index. As you can see it doesn't.

At first i though it would have to be something to do with the computed date but even without the date filter the indexes won't be used. I tried several variations of indexes but i am nog getting much further. What am i missing here?

The relevant table indexes are:

ALTER TABLE exhibitions ADD INDEX(slug);
ALTER TABLE exhibitions ADD INDEX(venue_id, ends);

ALTER TABLE temperatures ADD INDEX(ref);

And, as requested, the Exhibition create table query as well:

CREATE TABLE `exhibitions` (
  `id` char(36) NOT NULL,
  `clue` char(6) NOT NULL DEFAULT '',
  `venue_id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `starts` date DEFAULT NULL,
  `ends` date DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `user_id` char(36) NOT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT '0',
  `permanent` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `active` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `cidn` varchar(255) DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `slug` (`slug`),
  KEY `cidn` (`cidn`),
  KEY `venue_id` (`venue_id`,`ends`),
  KEY `venue_id2` (`venue_id`),
  FULLTEXT KEY `name` (`name`,`description`,`url`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Added statistics:

mysql> SELECT  *
-> FROM    information_schema.statistics
-> WHERE   UPPER(table_name) = UPPER('temperatures')
->         AND UPPER(column_name) = UPPER('ref');
+---------------+--------------+--------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME   | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT |
+---------------+--------------+--------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+
| NULL          | narb         | temperatures |          0 | narb         | unique_ref |            1 | ref         | A         |        2655 |     NULL | NULL   |          | BTREE      |         | 
| NULL          | narb         | temperatures |          1 | narb         | ref        |            1 | ref         | A         |        2655 |     NULL | NULL   |          | BTREE      |         | 
+---------------+--------------+--------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+
2 rows in set (0.02 sec)

Updated with result of Quassnoi's sugesstion:

mysql> EXPLAIN
-> SELECT  Exhibition.venue_id, Exhibition.name, Exhibition.slug,
->         Venue.name, Venue.slug, Venue.location_id,
->         Venue.id, Exhibition.id
-> FROM    exhibitions AS Exhibition
-> LEFT JOIN
->         venues AS Venue
-> ON      Venue.id = Exhibition.venue_id
-> LEFT JOIN
->         temperatures AS Temperature FORCE INDEX (unique_ref)
-> ON      Temperature.ref = Exhibition.id
-> WHERE   Exhibition.active = '1'
->         AND Exhibition.ends <= CURDATE()
-> ORDER BY
->         Temperature.temperature DESC
-> LIMIT 5;
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Exhibition  | ALL    | NULL          | NULL    | NULL    | NULL                     | 1536 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Venue       | eq_ref | PRIMARY       | PRIMARY | 108     | narb.Exhibition.venue_id |    1 |                                              | 
|  1 | SIMPLE      | Temperature | ALL    | NULL          | NULL    | NULL    | NULL                     | 2662 |                                              | 
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

added create table for temperatures:

CREATE TABLE `temperatures` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ref` char(36) NOT NULL,
  `views` int(10) unsigned NOT NULL,
  `ratings` int(10) unsigned NOT NULL,
  `comments` int(10) unsigned NOT NULL,
  `bookmarks` int(10) unsigned NOT NULL,
  `tags` int(10) unsigned NOT NULL,
  `collected` int(10) unsigned NOT NULL default '0',
  `trips` int(10) unsigned NOT NULL,
  `fans` int(10) unsigned NOT NULL,
  `temperature` int(10) NOT NULL default '1000',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique_ref` (`ref`),
  KEY `ref` (`ref`)
) ENGINE=MyISAM AUTO_INCREMENT=2743 DEFAULT CHARSET=latin1;

Answer 1

You should make sure your index matches your join order. If you're using multiple criteria or multiple joins, MySQL will look for a matching index. For instance:

Let's say you have an index set up with the following fields: Exhibition.venue_id, Exhibition.active, Exhibition.ends

If your select statement is

SELECT fields FROM exhibitions WHERE Exhibition.venue_id > 10 AND Exhibition.active = 'T' AND Exhibition.ends < '2009/9/23'

Then your index should be used. if you change the order of your criteria, your index may not be used, which can cause the issues you're seeing.

Also, if you have an index on the field active which is a boolean value, it doesn't add much value to your search unless there are typically less active exhibitions then inactive exhibitions. If you don't have a ton of records, the optimizer might also discard the index usage because it figures a scan is more efficient.

I would generate 10,000 records and then rerun it. See if it still isn't using the index.

-Chris

Answer 2

Could you please post the result of this query:

SELECT  *
FROM    information_schema.statistics
WHERE   UPPER(table_name) = UPPER('temperatures')
        AND UPPER(column_name) = UPPER('ref')

Update 3:

Your exhibition table is defined as CHARACTER SET UTF8, while temperatures is defined as CHARACTER SET LATIN1.

Since UTF8 is not implicitly convertible to LATIN1, an implicit COLLATE UTF8 is added to Temperature.ref, making it unsargable and the index unusable.

You need to either rewrite your query as this:

SELECT  Exhibition.venue_id, Exhibition.name, Exhibition.slug,
        Venue.name, Venue.slug, Venue.location_id,
        Venue.id, Exhibition.id
FROM    exhibitions AS Exhibition
LEFT JOIN
        venues AS Venue
ON      Venue.id = Exhibition.venue_id
LEFT JOIN
        temperatures AS Temperature FORCE INDEX (unique_ref)
ON      Temperature.ref = CAST(Exhibition.id AS CHAR CHARACTER SET latin1)
WHERE   Exhibition.active = '1'
        AND Exhibition.ends <= CURDATE()
ORDER BY
        Temperature.temperature DESC
LIMIT 5

, or, better, convert temperatures into UTF8:

ALTER TABLE temperatures MODIFY COLUMN ref CHAR(36) CHARACTER SET utf8 NOT NULL, CHARACTER SET utf8