Queries which use composit indexes


#1

Hi Guys,
I’ve created an index like this:
*** create index byName on user(name,family)

How can I write a query which uses that index to retrieve my result?
– select * from user where name=‘afshin’ and family=‘afzali’

What are the rules that I have to follow to be able to use my intended indexes?

BEST
~ Afshin


#2

Hi Afshin,

That composite index should be used. For best results, try to put the most selective first in the index definition. For example, if family is likely to be most selective, then create the index on user(family, name).

Use EXPLAIN to see that your index is being used.

Thanks,
Gerald


#3

Thanks a lot Gerald,

Happy Christmas :slight_smile:


#4

Happy New Year, Afshin :smile: