MySQL Forums
Forum List  »  MyISAM

Index Keys of the table
Posted by: fei lee
Date: October 28, 2012 11:01PM

Dear Sir/Madam,

I have table structure as following:


CREATE TABLE IF NOT EXISTS `contacts` (
`ContactID` varchar(30) NOT NULL,
`Name` varchar(30) NOT NULL,
`Gender` char(1) NOT NULL,
`Phone` varchar(16) NOT NULL,
`Email` varchar(50) NOT NULL,
`Address` varchar(100) NOT NULL,
`DOB` varchar(8) NOT NULL
PRIMARY KEY (`ContactID`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Users can search the contact with following fields:
- Name
- Gender
- Phone
- Email
- DOB

Possible SELECT queries are:
SELECT * FROM contacts WHERE Name = ?;
SELECT * FROM contacts WHERE Phone = ?;
SELECT * FROM contacts WHERE Name = ? AND Gender = ?;
SELECT * FROM contacts WHERE Phone = ? AND Email = ?;
SELECT * FROM contacts WHERE Name = ? AND Gender = ? AND Phone = ?;

I wonder whether I should create an index key with all searching fields, or create index keys one by one.

As I understand a 'grouped' index keys could give higher performance rather than individual index keys.
However, since the users may search different fields each time, how should I manage my index keys for good/high performance?

Should I create the KEY as following?
KEY(Name, Gender, Phone, Email, DOB)

OR following will be better?
KEY1(Name)
KEY2(Gender)
KEY3(Phone)
KEY4(Email)
KEY5(DOB)

Your comments will be highly appreciated!

Options: ReplyQuote


Subject
Views
Written By
Posted
Index Keys of the table
2733
October 28, 2012 11:01PM
1554
October 30, 2012 11:59AM
1504
November 01, 2012 08:05PM
1580
November 02, 2012 08:21PM
1250
November 04, 2012 08:54PM


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.