Subject Re: [firebird-support] What is duplicate index?
Author Helen Borrie
At 01:21 PM 23/08/2006, you wrote:
>I understand that duplicate index can slow down firebird.


> What is duplicate index?

Two different things, both of which need to be avoided.-

a) An index that is a duplicate of one that already
exists. Typically, this situation will exist if you use CREATE INDEX
to create an index on the same keys on which your you have a primary,
foreign key or unique constraint. Indexes are created automatically
to support the constraints and you can't prevent them from being
created. However, the optimizer can be confused when it finds two
(or more) identical indexes and may avoid using an index
altogether. So, in this sense, some queries could be slower than
they need to be, due to this confusion.

2) An index that contains large numbers of duplicate entries
involving a small number of possible values. This can REALLY slow
down queries.

>How can one create duplicate index? example please

An extreme example of 2) might occur where you have a Voters table
with a column CountryOfBirth. You decide to index this column (or
place a Foreign Key on it). However, in your system, most of the
Voters were born in the same country. Your index will form a huge
chain of duplicates, which will cause it to take hours to finish a
"WHERE CountryOfBirth = ?" search on most electoral systems except
possibly that of some remote island principality.