CODEWITHSUNDEEP
mysqlindexing
fuzhongyu
fuzhongyu

Reputation: 31

mysql index: possible_keys is null but key isn't null

Today, there is a problem using a composite index, such as (name, age, DESC), and now go to query "select * from table ThisTableName where age>15 and desc='test' ". In this case, the B-Tree index is not in line with the leftmost matching principle, the index possible_keys for null can be understood, but key has shown that the composite index. Why this is the case, possible_keys is representative of the index can be used, if this value is null, key value in principle only for null.

Upvotes: 3

Views: 2363

Answers (2)

tecdoc ukr net
tecdoc ukr net

Reputation: 134

In my case when possible_keys is null but index has been set - only deleting the index and creating the index with a new name helped!

CHECK TABLE _table_name_ QUICK and REPAIR TABLE _table_name_ QUICK did not help

Upvotes: 0

Tom Briggs
Tom Briggs

Reputation: 53

I think this snippet from the MySQL documentation explains it:

It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

Upvotes: 4

Related Questions