MySQL Forums
Forum List  »  Performance

Why is wrong index being selected
Posted by: Steve Bell
Date: February 23, 2016 01:54PM

I have a table of contract data which has 2 indexes on it plus the primary index.

Index 1 consists of:
- Status (int)
- Customer Reference (varchar)
- Policy Reference (varchar)
- Id (int)

Index 2 consists of:
- Status (int)
- Group Reference (varchar)
- Id (int)

The select query is retrieving all columns from the table and filtering those rows where the Status < 300, Customer Reference <> '', Policy Reference <> '' and id is not null

If I run an explain on this query it says Index 2 will be used, which makes no sense as Group Reference is only used in terms of the columns being returned. It is not being used in restricting the data returned.

If I change the query so that instead of Status < 300 I change it to Status = 100, or any other number apart from zero (rest of query unchanged) the explain suggests index 2 will be used.

If I change the query to use Status = 0, rest of query unchanged, then explain suggests index 1 will be used.

I expected index 1 to be used in every case.

The version of MySQL we're using is 5.6.28

Any idea what is happening?

Thanks in advance

Options: ReplyQuote

Written By
Why is wrong index being selected
February 23, 2016 01:54PM
February 23, 2016 11:39PM
February 25, 2016 02:47AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.