MySQL Forums
Forum List  »  MyISAM

Re: Index Keys of the table
Posted by: Rick James
Date: October 30, 2012 11:59AM

Should I create the KEY as following?
KEY(Name, Gender, Phone, Email, DOB) -- Only good when "name=..." in included

OR following will be better?
KEY1(Name)
KEY2(Gender) -- rarely worthwhile indexing on low-cardinality fields
KEY3(Phone)
KEY4(Email)
KEY5(DOB)

Read about "compound" indexes here: http://mysql.rjweb.org/doc.php/index1

SELECT * FROM contacts WHERE Name = ?; -- INDEX(Name ...)
SELECT * FROM contacts WHERE Phone = ?; -- INDEX(Phone ...)
SELECT * FROM contacts WHERE Name = ? AND Gender = ?; -- Since Name is virtually unique, the test for Gender is unlikely to be useful
SELECT * FROM contacts WHERE Phone = ? AND Email = ?; -- INDEX(Phone, Email ...) or INDEX(Email, Phone, ...); but the issue of uniqueness makes a compound key not that useful.
SELECT * FROM contacts WHERE Name = ? AND Gender = ? AND Phone = ?; -- ditto

Assuming that there are very few duplicate names, duplicate phones, and duplicate emails, I recommend:
INDEX(Name)
INDEX(Phone)
INDEX(Email) -- (although you did not have a use case for this)
INDEX(DOB) -- (although you did not have a use case for this)

If a user can search just on Gender (WHERE Gender = 'M'), a table scan is almost as good as having an index.

If a user can search for things other than exact equality (eg, WHERE Name LIKE 'Lee%'), then my recommendations may or may not be good enough. Please provide any such, so we can discuss further. Also, how many rows in the table?

Options: ReplyQuote


Subject
Views
Written By
Posted
3015
October 28, 2012 11:01PM
Re: Index Keys of the table
1730
October 30, 2012 11:59AM
1637
November 01, 2012 08:05PM
1700
November 02, 2012 08:21PM
1385
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.