Index Performance

Go Up to IBConsole - Performance Guidelines

Index Performance consists of indices and multiple indices:

Indices

InterBase index management is different from other Relational Database vendors. InterBase can use indices as both navigational or bit-mapped. Navigational means that the index is stepped through value by value in whatever order it was defined. This allows a query to access the first record very quickly if there is an index defined that matches the ORDER BY clause of the query. Bit-mapped means that multiple indices can be used to match the JOIN and/or ORDER BY clauses and that each index is scanned for matching values before all the indices are combined using an Boolean OR operation.

Multiple Indices

Indices are probably the most important part of the tuning your InterBase database. As you can see InterBase depends heavily on indices for query optimization. No indices are defined by default, but with SQL Declarative Referential Integrity syntax, the SQL CREATE TABLE will create unique indices for the primary key fields and duplicate indices for the foreign key fields.

CREATE TABLE foo (
               foo_num INTEGER,
               foo2_num INTEGER,
               PRIMARY KEY (foo_num),
               FOREIGN KEY (foo2_num) REFERENCES foo2 (foo2_num));

Because of this you must be very careful in defining your own indices as InterBase will use multiple indices to resolve queries. This can result in a degradation of performance if multiple indices reference the same fields as needed by the query. An example would be if the primary key of table first_table is made of three fields, field_1, field_2, and field_3. Field_2 is a foreign key into table second_table and field_3 is a foreign key into table third_table. Using the Declarative Referential Integrity syntax above, the following indices would be created:

RDB$PRIMARY1 UNIQUE ASC (field_1, field_2, field_3);
RDB$FOREIGN1 ASC (field_2);
RDB$FOREIGN2 ASC (field_3);

Now, whenever you queried first_table and supplied values for all three fields, all three indices would be used when you only really need the unique index. There is an alternative if you really need to access fields field_2 and field_3 independently of field_1, and you want to enforce uniqueness for first_table. The alternative is to drop the unique index on the three fields and create an artificially generated unique id field, field_X, with a unique index on that field and a duplicate index on field field_1. A trigger would have to be defined to generate the value for the id field field_X.

CREATE TABLE first_table (
                               field_X INTEGER,
                               field_1 INTEGER,
                               field_2 INTEGER,
                               field_3 INTEGER,
                               PRIMARY KEY (field_X),
                               FOREIGN KEY (field_2) REFERENCES second_table (field_2),
                               FOREIGN KEY (field_3) REFERENCES third_table (field_3));
CREATE ASC INDEX first_table_fx1 ON first_table(field_1);

CREATE GENERATOR first_table_id;

SET TERM !! ;
CREATE TRIGGER first_table_unique_id FOR first_table
BEFORE INSERT
POSITION 0 AS
BEGIN
                               new.field_X = gen_id ( first_table_id, 1 );
END;
SET TERM ;!!

Next

• Multi-Generational Architecture