This just gives you a basic idea of when and when not to index fields. Although the suggestions below are mostly agreed, over-doing "good" things leads "bad" results so it is your responsibility to debug your queries to find out if your index setup really serve the purpose or not.



Every index on a table causes performance decrease for insert and update operations because they get updated every time the data changes. So index a field only if you are going to use it in WHERE, JOIN, ORDER BY, GROUP BY and HAVING clauses. Don't index a field just because it is considered as a "good candidate". E.g. timestamp field is a good candidate because it has a high degree of uniqueness but if you are not planning to use it in your queries then do not index it.


Consider using The Slow Query Log and EXPLAIN to investigate how queries would perform.