homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Muli-Column Index Question

Msg#: 4420102 posted 6:26 pm on Feb 21, 2012 (gmt 0)

Hello Everyone,

Trying to figure out exactly how to optimize my table indexes. To start, my MySQL table is 1 million+ records w/ over 80+ columns. From that, quite a few of my searches are very similar in nature but just adding and subtracting columns being searched.

For example:

Main category search would go through the following columns:
County => ItemStatus

Inner category searches would go through more columns, like so:
County => ItemStatus => ItemType
County => ItemStatus => ItemType => ItemSubType
County => ItemStatus => ItemType => ItemSubType => SpecialConditions

My Questions Regarding Building Indexes:

1) Should I create an index to cover the most in-depth search of the 5 columns and then have the other searches use that same index? (I believe the indexes are backward compatible like this as long as the main column/index stays the same....may be wrong, however)

2) Do creating a multi-column index eliminate the need for a single column index on the "root" column in the index?

For example, if i had an index that was ordered like:

County => ItemStatus => ItemType => ItemSubType => SpecialConditions

Would I need to have a separate index on County for any reason?

Hope that makes sense and thank you in advance for your responses.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved