Hi Aaron!

> Let's take a simple example: a table with 50,000 records which has a
> VALUE and an ID (like a state table but much bigger). Sometimes, I
> will query on the VALUE and look for a list of matching IDs that
> will be used to retrieve VALUEs from another table by a join. In
> this case, I would index (VALUE, ID) to drive the query from the
> VALUE to the ID, and possibly allow the query to use just the index,
> since the index would contain all of the data required for the
> query.

This could be 'very OK', especially if there is a possibility of many
duplicates on VALUE (see below).

> In other queries, I would be using this same table as a lookup to
> display appropriate values for a list of IDs, again through a join.
> For this purpose, I put another index on the table, (ID, VALUE). The
> cost of the two indexes is worth the price of inserts based on our
> benchmarks and we get good performance in SQLServer.

If ID is a primary key (PK), then this index is at best useless. Ann
stated that there was a fix in Fb 1.5 when there was a unique index
available (and a PK is unique). I'm still primarily using Firebird
1.0, and one of the problems I often face is with my queries is that
Firebird wants to use too many indexes within my query (e.g. even if I
have an almost unique index, Firebird may still try to use both this
almost unique index AND an index on a field having only a few possible
values. This dramatically increases the time needed to execute the
query).

> It seems that in Firebird, singleton indexes on ID and VALUE might
> be best, since as point 4 below makes clear, database versioning
> would require the table to be read anyway.

You forgot one little thing. If there are potentially many duplicates
on VALUE, then an index only on this field will make finding a
particular record more difficult. This means that UPDATE and DELETE
operation will take more time. Hence, from the little I know of your
case, I think I would recommend having an index on (VALUE, ID) and
only have ID as the PK and not specify any additional index on that
field. By the way, the (VALUE, ID) index will also be used when you're
only interested in the first field(s) of the index (VALUE in this
case) and having two separate indexes (VALUE) and (VALUE, ID) will
only confuse the optimiser. The reverse is not true, the (VALUE, ID)
index cannot be used if your only interested in ID.

Finding which indexes to create and which not to create are two
equally important sciences of Firebird ;-}. Do it right and your users
will be impressed, do it wrong and you'll draw the (wrong) conclusion
that Firebird a useless database.

Set