CODEWITHSUNDEEP
mysqlindexing
Zelong
Zelong

Reputation: 2556

Index is not used in MySQL even if the query contains an index hint

Consider the following table:

create table mytbl (
    id long primary key,
    digest varchar(32)
);

The digest column contains MD5 digest and I created index on digest by:

create fulltext index digest on mytbl (digest);

I subsequently check a query:

explain select count(id) from mytbl where digest = 'abcde12345ffhhg';  -- a digest in the table

The plan is (the table has 1000 rows):

id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
1  | SIMPLE      | mytbl | ALL  | digest        | NULL| NULL    | NULL| 1000 | Using where

I then called the query

explain select count(id) from mytbl use index (digest) where digest = 'abcde12345ffhhg';

But the plan remained the same, i.e. the index digest is not used.

Why does MySQL ignore the index set on digest?

EDIT

I thought I had a misconception about the option [fulltext | spatial | unique] in CREATE INDEX statement. I dropped the fulltext index and then created a default index (create index digest ...). Consequently, MySQL used the digest index in the query plan.

Upvotes: 2

Views: 233

Answers (1)

Redbeard011010
Redbeard011010

Reputation: 964

This query ignores the fulltext index because it is not explaining the execution plan of a fulltext search.

To instead explain a fulltext search, and engage the index, use MATCH()...AGAINST():

EXPLAIN SELECT count(id) FROM mytbl WHERE MATCH(digest) AGAINST ('abcde12345ffhhg');

Upvotes: 1

Related Questions