Page 1 of 1

question about indexing

Posted: Fri Apr 23, 2010 6:07 pm
by yacahuma
I am writing an application that will have search page. The page has firstname, secondname, lastname and lastname2 as separate fields instead of just one name search field. How should I index the table

option1:
KEY `person_fullname` USING BTREE (`firstname`,`secondname`,`lastname`,`lastname2`)

option2:
KEY `firstname` (`firstname`),
KEY `secondname` (`secondname`),
KEY `lastname` (`lastname`),
KEY `lastname2` (`lastname2`)

Is there a difference?
Thank you

Re: question about indexing

Posted: Sun Apr 25, 2010 3:19 pm
by Darhazer
The first index won't be used if you are searching for lastname2 only
It will be used if you are searching for firstname, firstname and secondname, firstname secondname and lastname or all of the columns
You can always use EXPLAIN command to check the used index (and if the index is used at all)

Re: question about indexing

Posted: Sun Apr 25, 2010 10:07 pm
by yacahuma
thank you.