Welcome to WebmasterWorld Guest from 54.163.100.58

Forum Moderators: open

Muli-Column Index Question

   
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

My Threads

Hot Threads This Week

Hot Threads This Month