How to create index in firebird for improvement of

Question

I am newbie in using firebird. I have 3 tables.

for T_TABLE1 the structure is shown below:

for T_TABLE2 the structure is shown below:

and for T_TABLE3 the structure is like this:

the primary key is the ID from T_TABLE1 to TBL1_ID in T_TABEL2 and TBL1_ID in T_TABLE3. How can I select data join with that 3 tables and I want to use index for best select query but I don't know how to create index because I am newbie in using firebird and I want to learn more in using firebird. Hope my explanation is clear.

Answer 1

The syntax for creating an index is documented in The Firebird 2.5 Language Reference, CREATE INDEX, it also contains additional information.

The syntax for creating an index is:

CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
   ON tablename
   { (<col> [, <col> ...]) | COMPUTED BY (expression) }

<col>  ::=  a column not of type ARRAY, BLOB or COMPUTED BY

So for creating an (ascending) index on T_TABEL2.TBL1_ID you would do:

CREATE INDEX ix_tabel2_tbl1_id ON T_TABEL2 (TBL1_ID)

But as I commented earlier today that is not necessary if there is a foreign key on this column, as foreign keys in Firebird automatically get an index (the same applies to primary keys).

It is not clear from your question if you have foreign keys, but I'd advise you to create them instead of an index: it gives you the index and on top of that enforces that the value of TBL1_ID actually exists in T_TABLE1.

Just keep in mind that creating an index does not automatically improve performance. The optimizer for example might decide that using an index is not worth the effort (or that a specific index is not relevant for the query).