Writing the previous post it came to my mind, that many people may not be aware that using custom functions in WHERE clauses may be the reason why the index is not used. There are several facts about indexes you should keep in mind:
- index is most often a b-tree, see one of the Princeton’s lectures about balanced trees;
- create an index only if the cardinality of the column is large, i.e., do not create an index on a column that has only two values as it will be more efficient to scan the whole table;
- wild-card at the beginning of the LIKE clause will prevent it from using an index;
- while joining two tables you do not need to have index on both of them;
- creating index on (a,b) is not the same as on (b,a) or two separate indexes on a and b;
- execution plan is important – check if your indexes are used as you wanted them to.
If you want to learn more about indexes, how do they work, and how to use them properly, check www.use-the-index-luke.com by Markus Winand.