CODEWITHSUNDEEP
mysqlsqlindexingprimary-key
pillarOfLight
pillarOfLight

Reputation: 8982

why would MySQL not use keys when there are possible keys

So whenever I EXPLAIN my query, I often get instances in which it states certain fields as possible keys but then the key table will be null...

Why would MySQL do this and decide to use no keys when in fact it can use a possible key?

Upvotes: 2

Views: 1755

Answers (2)

Keith Randall
Keith Randall

Reputation: 23265

It can't always use a key, for instance:

SELECT * FROM A, B where A.id = B.id;

Although id is a possible key for both lookups, it can't be used for both. One of the tables must do a full scan.

Upvotes: 5

Robbie Dee
Robbie Dee

Reputation: 1977

The optimizer may reasonably calculate that using an index would result in poorer performance than say a full table scan.

I'll give you an example:

Say, you have a gender field which is indexed but 90% of your records are female. The optimizer could use the index but it is probably more efficient just to do a full table scan as the spread of the data is skewed.

Upvotes: 8

Related Questions