Multi-Column Indexes - Firebird

If your applications frequently need to search, order, or group on the same group of multiple columns in a particular table, it will be of benefit to create a multi-column index (also known as a composite or complex index).

The optimizer will use a subset of the segments of a multicolumn index to optimize a query if the index. However, queries do not need to be constructed with the exact column list that is defined in the index in order for it to be available to the optimizer. The index can also be used if the subset of columns used in the ORDER BY clause begins with the first column in the multicolumn index.

Firebird can use a single element of composite index to optimize a search if all of the preceding elements of the index are also used. Consider a segmented index on three columns, Col_w, Col_x and Col_y, in that order, as shown in Figure.

Using a segmented index

Using a segmented index

The index would be picked by the optimizer for this query:

SELECT <list of columns> FROM ATABLE ORDER BY COL_w, COL_x;

It would not be picked for either of these queries:

SELECT <list of columns> FROM ATABLE ORDER BY COL_x, COL_y; /**/ SELECT <list of columns> FROM ATABLE ORDER BY COL_x, COL_w;

OR Predicates in Queries

If you expect to issue frequent queries against a table where the queries use the OR operator, it is better to create a single-column index for each condition. Since multicolumn indexes are sorted hierarchically, a query that is looking for any one of two or more conditions must search the whole table, losing the advantage of an index.

For example, suppose the search requested

... WHERE A > 10000 OR B < 300 OR C BETWEEN 40 AND 80 ...

An index on (A,B,C) would be used to find rows containing eligible values of A, but it could not be used for searching for B or C values. By contrast, individual indexes on A, B, and C would all be used. For A, a descending index would be more useful than an ascending one if the search value is at the high end of a range of stored values.

Search Criteria

The same rules that apply to the ORDER BY clause also apply to queries containing a WHERE clause. The next example creates a multicolumn index for the PROJECT table in employee.gdb:

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);

The optimizer will pick the PRODTYPEX index for this query, because the WHERE clause refers to the first segment of the index:

SELECT * FROM PROJECT WHERE PRODUCT ='software';

Conversely, it will ignore the index for the next query, because PROJ_NAME is not the first segment:

SELECT * FROM PROJECT WHERE PROJ_NAME STARTING WITH 'Firebird 1';

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics