Add indices for an advanced search option
I've taken over a database that lists products and there is currently around 6k records but it grows by about 2k per year. Broadly speaking, each product has a single category field (GUID) and then six other fields relating to other areas of products.
When you click on a page it shows a category so I thought it prudent to index that although to be fair it seems to run fine without. The cardinality of the category column is about 60.
There is an advanced search where you can then optionally choose to filter the other six fields so you always filter by category the other six fields are optional and could be in any number or combination.
The noteworthy point is there is an int flag called status of 0, 1 or 2 corresponding to invisible, for sale, archived respectively.
Databases are my weak point so forgive these questions if they're stupid:
1. With MySQL being quite fast, is there a rule-of-thumb for when to add an index in terms of number of records? Was I right to add one at ~6,000 records?
2. Am I right in saying the index on the category effectively separates the ~6,000 records into ~60 different sections. If we assumed the categories were evenly spread, performance-wise it's almost as good as querying a single table of 100 records as the index means MySQL “knows” where the correct 100 are. Is that how it works? In which case, there is no need to index any of the other six fields, right?
3. I've read that an index on the status field would be a bad idea since the cardinality is 3. Why is this? Again, is there any rule-of-thumb as to when to add an index?
Generally speaking, with MySQL are you okay up to 10,000 records even on a shared server?