question about indexing

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

question about indexing

Post 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
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: question about indexing

Post 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)
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

Re: question about indexing

Post by yacahuma »

thank you.
Post Reply