Hi,

I'm evaluating Firebird 1.5 and need some hints about how I should index
my tables for optimum performance.

I have a db that's practically read-only, so I can ignore costs for
maintaining indexes when doing updates.

The DB is about 2 Gbyte in size. One master table with about 1 million
records and about 50 columns. About half of the columns are only used
for output data, i.e. in the select part, and these are 5-80 characters.

The other half of the columns are used only in the where part and are
rather short: 1-5 characters.

A few child tables with 2-12 child records for each master record.

I will be doing selects like this:

select count(*),
count(Master.Field1),
count(Master.Field2),
...
from Master
inner join Detail1 on Master.Id = Detail1.Id
inner join Detail2 on Master.Id = Detail2.Id
where (Master.FieldM1 = 'value_M1_1'
or Master.FieldM1 = 'value_M1_2'
...)
and (Master.FieldM2 = 'value_M2_1'
or Master.FieldM2 = 'value_M2_2'
...)
and (Detail1.FieldD1 = 'value_D1_1'
or Detail1.FieldD1 = 'value_D1_2'
...)
and (Detail2.FieldD2 = 'value_D2_1'
or Detail2.FieldD2 = 'value_D2_2'
...)
group by Master.Id

This will be executed, by each user, multiple times until he is
satisfied with the returned counts. He will then execute the actual data
select once, using the same criteria:

select Field1,
Field2,
...
from ...
where ...

I need both of these queries to execute as quickly as possible.

I cannot know beforehand exactly which columns will be used in the where
part. The user can choose to base the query on an arbitrary subset of
the available columns. Note that I only want distinct master records in
the output, hence the group by. (Child tables are only used in the where
part).

How should I index this DB?

I know I can rewrite the query using exists for criteria in child
tables. Would this improve performance? And if I do that, how should I
index?

The number of returned records would usually be a few hundred to a
couple of thousand, but could potentially go up to tens or even hundreds
of thousands.

Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64