MySQL is not using an index

问题:

I have the following table

CREATE TABLE `Config` (
   `id` mediumint(9) NOT NULL AUTO_INCREMENT,
   `type_id` mediumint(9) DEFAULT NULL,
   `content_id` mediumint(9) DEFAULT NULL,
   `menu_id` int(11) DEFAULT NULL,
   `field` varchar(50) NOT NULL DEFAULT '',
   `value` text NOT NULL,
   PRIMARY KEY (`id`),
   KEY `menu_id` (`menu_id`) USING BTREE,
   KEY `type_id` (`type_id`,`content_id`,`menu_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1;

It's filled with about 800k rows of test data. Whenever I run the following query it takes about 0.4 seconds to complete:

SELECT id, content_id, menu_id, field, `value`
FROM Config
WHERE type_id = ?
AND content_id = ?

An explain tells me, MySQL is doing a full tablescan instead of using an index:

id select_type table   type possible_keys key key_len ref   rows    Extra
1   SIMPLE     Config  ALL                  792674  Using where

Can someone please explain what I am doing wrong here? How has the index to be like so it's used here? Sometimes the query has the extra condition AND menu_id = ?, which should benefit from it, too.

回答1:

I had a problem once with a query where it doesn't use the index that I specified. It turned out, MySQL won't use your index if the result (of your query) exceeds certain rows. For an example, if the result itself is taking a lot of your total rows, it won't use your index. However, I don't have the specific percentage. You could try adjusting the query to return smaller result to test this theory.

My question about the problem: MySQL datetime index is not working

回答2:

0.4s isn't bad for 800,000 rows. The MySQL optimiser may determine it doesn't need your indexes.

You could try using "hints" to see if you can change performance outcomes:

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

回答3:

The accepted answer is actually right, but if you want your MySQL to use the Index regardless the matches rows, you can specify the FORCE INDEX (index_name) command.