Welcome to WebmasterWorld Guest from 54.163.35.238

Forum Moderators: open

Message Too Old, No Replies

Muli-Column Index Question

     

tec4

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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month