CODEWITHSUNDEEP
mysqlindexing
Denny
Denny

Reputation: 459

using non index with composite index

I have question if I have tables with 3 columns (firstname, lastname , address ) as string/varchar(255)

and I have composite my_idx with 2 columns

CREATE INDEX my_idx ON my_table (firstname,lastname)

if I use sql , will it use my defined index ?

select * from my_table where address="zzz" and firstname="xxxx" and lastname="yyyy"

or should I use index columns as first left most condition 

select * from my_table where  firstname="xxxx" and lastname="yyyy" and address="zzz"

Thank you

Upvotes: 0

Views: 56

Answers (1)

Martin Schneider
Martin Schneider

Reputation: 3268

First of all: if you prepend your Query with the keyword "EXPLAIN" it will print out all the indices it may use and which one MySQL choose. From my understanding, yes it will use the index. The order of the fields in the Query is not relevant.

What matters is the order in the Index, but only if you are not providing all fields in the Query (or applying a function to the value or using e.g. the like operator for the rest of a string). If for example you only queried for lastname, the index can not be used. If you only queried for firstname, the index will be used. If you queried for firstname and address, the index will be used and so on...

Upvotes: 1

Related Questions