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.
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.