Niben,

> So I wonder how important is to have composite index with multiple
> columns? Is it even necessary in Firebird?

It is very necessary!

It just isn't always necessary. ;-]

It depends on:

- number of unique elements which are fields to be indexed

- number of rows which share the unique values

- the common access paths you use for retrieving data.


Consider a table with 10 million rows with Channel and LogDate values
(there are 10 unique Channel values and 1000 unique LogDate values).

Now consider that you commonly search for rows using the following
criteria:

WHERE Channel = X AND LogDate = A, or
WHERE Channel = X AND LogDate BETWEEN A AND B

If we consider the WHERE Channel = X AND LogDate = A query:

If you create single indexes then the engine needs to create a bitmap of
1 million records for the Channel = X criteria and then compare that to
the LogDate index bitmap of 10,000 rows.

Whereas if you have a compound index (Channel, LogDate), then the engine
more easily navigate the index to find the appropriate section(s) of the
index, and the ~1000 rows which matter.

This is a HUGE performance increase -- I have seen it for myself.


Sean