A good index should be on a column that is often used in the “where” clause of queries or the “on” clause of a join. It should be on a column that has a wide range of possible values (e.g. nearly every row has a different value), rather than only a few possible values (like “active”, “inactive”, “expired”).
An index will very slightly slow down inserts, updates and deletes that affect the indexed column, but this is rarely a problem. It’s one reason why you don’t want too many indexes though.
An index will take up some disk space. Again, this is rarely a problem. It’s another reason why you don’t want too many indexes though.
There are special types of indexes you can use for special purposes, like finding words in columns containing text or columns with geographical data.
Latest Answers