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

Question

MySQL 5.6

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.

Not sure if this is related, but seems to be...from dev.mysql.com/doc/refman/5.6/en/index-hints.html " For FULLTEXT searches, index hints work as follows: For natural language mode searches, index hints are silently ignored. For example, IGNORE INDEX(i1) is ignored with no warning and the index is still used. Which may indicate that MySQL is ignoring your index hint. Still doesn't explain why it doesn't use the index without the hint... but another post I read said that for a low number of rows, MySQL might opt not to use an index, 'of its own accord' so to speak. — Redbeard011010
– Redbeard011010, Commented Dec 26, 2015 at 6:23
For small number of rows it may be faster to scan the table rather than check an index. Or the statistics might not be up to date. — Sami Kuhmonen
– Sami Kuhmonen, Commented Dec 26, 2015 at 6:24
@SamiKuhmonen Thanks. The table in the question is just an example. The real table is over 100K rows but the index digest was not used at all. — Zelong
– Zelong, Commented Dec 26, 2015 at 6:25
@Redbeard011010 Thanks a lot. I tried to explain select on the column id and the PRIMARY index was indeed used. — Zelong
– Zelong, Commented Dec 26, 2015 at 6:29
Try this, please. explain select count(id) from mytbl where match(digest) against ('abcde12345ffhhg'); — Redbeard011010
– Redbeard011010, Commented Dec 26, 2015 at 6:35

Redbeard011010 · Accepted Answer · 2015-12-26 06:48:31Z

1

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');

edited Dec 26, 2015 at 6:48

answered Dec 26, 2015 at 6:28

Redbeard011010

9646 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

EDIT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related