Getting MySQL to use an index/key, 1 column in where and 2 in order by

How do I get MySQL to use a key/index with the following table structure and query?

-- the table
CREATE TABLE `country` (
  `id` int(11) NOT NULL auto_increment,
  `expiry_date` datetime NOT NULL,
  `name` varchar(50) collate utf8_unicode_ci NOT NULL,
  `symbol` varchar(5) collate utf8_unicode_ci NOT NULL,
  `exchange_rate` decimal(11,5) NOT NULL default '1.00000',
  `code` char(3) collate utf8_unicode_ci NOT NULL,
  `currency_code` varchar(3) collate utf8_unicode_ci NOT NULL,
  `display_order` smallint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `code` (`code`),
  KEY `currency_code` (`currency_code`),
  KEY `display_order` (`expiry_date`,`name`,`display_order`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- the query
SELECT `country`.* 
FROM `country` 
WHERE `country`.`expiry_date` = 0 
ORDER BY `country`.`display_order` ASC, `country`.`name` ASC;

I'm trying to get it to use a key because the query with 180 in the result takes 0.0013s and is by far the slowest query on the page (3x longer than the next slowest).开发者_StackOverflow From my understanding, the query should use the display_order index/key.

Change it to:

CREATE TABLE `country` (
  `id` int(11) NOT NULL auto_increment,
  `expiry_date` datetime NOT NULL,
  `name` varchar(50) collate utf8_unicode_ci NOT NULL,
  `symbol` varchar(5) collate utf8_unicode_ci NOT NULL,
  `exchange_rate` decimal(11,5) NOT NULL default '1.00000',
  `code` char(3) collate utf8_unicode_ci NOT NULL,
  `currency_code` varchar(3) collate utf8_unicode_ci NOT NULL,
  `display_order` smallint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `code` (`code`),
  KEY `currency_code` (`currency_code`),
  KEY `expiry` (`expiry_date`,`name`,`display_order`) <<- renamed key for clarity
  /* always name compound keys for their left-most parts*/ 
  KEY `name` (`name`)  <<-- new key here
  KEY `display` (`display_order`) <<--new key here
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- the query
SELECT `country`.* 
FROM `country` 
WHERE `country`.`expiry_date` = 0 
ORDER BY `country`.`display_order` ASC, `country`.`name` ASC;

Compound indexes are tricky
MySQL did not use the index on name in the compound index, because name was in the middle and MySQL only uses parts of an index if that part is the left-most part of a compound index.
The same goes for the index on field display order. The compound index that has display_order in it uses that field as it's right-most part, and therefore will not sort.

Solution
Make a separate index for field name,
and a separate index for field display_order.

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

Also if a large percentage of rows have the same value for a field (> 40% (IIRC)) then MySQL will not use the index.

See: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

See: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
On how to force indexes as per FractalizeR suggestion.
Make sure to time your select after forcing the index
On such a simple query MySQL seems unlikely to be wrong, and your select time of 0.0013 seconds suggests that there are few rows in the table.
Indexes don't work as you'd expect when there are few rows in a table, because of the percentage rule stated above.

Note that in this case forcing the index would not have worked, because you cannot force MySQL to use the rightmost part of a compound index. It just cannot do that.

If you think MySQL chooses indexes unwisely and you are sure of that, use FORCE INDEX index hint: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Your query has an ORDER BY on columns {display_order}+{name}, while your index named "display_order" is in fact defined on columns {expiry_date}+{name}+{display_order}.

The order of columns in the index does matter. You can benefit an index if you need sorting of filtering on columns that are the beginning of the index. This become obvious if you keep in mind that index are pre-sorted information.

If you want to benefit an index on {display_order}+{name} then you need an index that begins with {display_order}+{name}. For example {display_order}+{name} or {display_order}+{name}+{expiry_date}.

So in order to optimize your query, you have to change your index in the table, or your SORT clause in the query.

last thing you can do is, use "FORCE INDEX" as mentionten by fractalizeR