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 开发者_JS百科  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.

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

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

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.