Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Muli-Column Index Question

6:26 pm on Feb 21, 2012 (gmt 0)

Junior Member

5+ Year Member

joined:June 16, 2011
posts: 79
votes: 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.

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members