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.