When and when not to index fields in database

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.

Do

• Only index fields that are used in WHERE, JOIN, ORDER BY, GROUP BY and HAVING clauses.


• If you used multiple fields to create a single index (e.g. INDEX(name, surname)), avoid indexing same fields individually (e.g. INDEX(name) or INDEX(surname)).


• Numeric data types (generally used for ascending or descending order) are considered as "good indexes" because numbers tend to be faster to evaluate compared to string data types. E.g. integer, date, timestamp so on.


• A field that has a high degree of uniqueness is considered as a "good candidate" for indexing. E.g. Primary Key and Foreign Key fields.

Don't

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.

Beneficial

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