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.
