Automatic vs. User-Defined Indexes - Firebird

Firebird creates indexes to enforce various integrity constraints automatically. To delete these indexes, it is necessary to drop the constraints that use them.

Use of the constraint indexes is not limited to their work supporting the integrity of keys and relationships. They are considered, along with all others, when queries are prepared.

When defining your own indexes, it is of utmost importance to avoid creating any index that duplicates an automatically generated one. It puts the optimizer (see the upcoming section “Query Plans”) in the unhappy position of having to choose between equals. In many cases, it will solve the problem by not choosing either of them.

Importing Legacy Indexes

Do not import “primary indexes” with tables from a migrating DBMS. There are two important reasons to abandon these indexes:

  • Many legacy systems use hierarchical index structures to implement referential integrity. SQL databases do not use this logic to implement referential integrity, and these indexes usually interfere with Firebird’s optimizer logic.
  • Firebird creates its own indexes to support primary and foreign key constraints, regardless of any existing index. As noted previously, duplicate indexes cause problems for the optimizer and should be avoided completely.

Directional Indexes

The sort direction of indexes in Firebird is important. It is a mistake to assume that the same index can be used to sort or search “both ways”—that, is lowest-to-highest and highest-to-lowest. As a rule of thumb, ASC (ascending) indexes will help searches where relatively low values are sought, whereas DESC (descending) indexes will help for maximum or high values.

If an automatic index is ASC (the default), there will be no problems if you need to define a DESC index using the same column(s). The reverse is also true: From v.1.5 onward, you can choose to have the automatic indexes for keys created in descending order. The optimizer will not be upset if you also create an ascending one on the same columns.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics