Generally speaking:
1. Do not add an index if you really do not need it.
Each index makes records slower ...
2. The index will be used where:
-- index on foo (bar) select bar from foo where bar = :bar;
In the same way, it will be used in references to foreign keys (in both tables).
-- index on foo (bar) if baz (bar) is frequently updated/deleted. create table foo (bar references baz (bar));
3. The index will be used for sorting, especially when it is bound to a limit:
-- index on foo (bar) select bar from foo order by bar limit 10;
4. Multi-column indexes are sometimes useful when the values 3. and 4 are applied.
In this case, first set the conditions, and the sort key will be the last:
-- index on foo (baz, bar) select bar from foo where baz between :baz1 and :baz2 group by bar;
5. Update your table statistics.
If table statistics are garbage, there is little chance that the optimizer will use your indexes. If necessary, manually delete / analyze your database.
6. The use of the index depends on your table partition.
Having passed a certain threshold of the extracted rows, it is faster to perform a full table scan. If your index is in a logical field that more or less divides your table into two parts, it will never be used.
Similarly, if your data is stored in such a way that an index scan is likely to accidentally access an almost always applicable disk page for this table, the scheduler will prefer a full table scan.
7. Consider partial / index expressions when available.
If you have the same value, with the exception of 10% of your rows, consider a partial index on it (i.e. where is not this value). This leads to a significantly lower index, without interfering with its real utility.
If you constantly request an expression to apply to your column, and your platform offers expression indexes, consider adding an index to it. When used, the expression will not be evaluated for each line.
Denis de Bernardy May 23 '11 at 14:38 2011-05-23 14:38
source share