Jan Agermose wrote:

> this idea of using a double index where one would other vise have used a
> single index not knowing better:
> 1) is it a interbase thing or does it work on other database managers
> 2) is it "simple" to explain why it works?

Your "selects" will probably be unaffected by the index - (logtype,id) or
(logtype). "Inserts" will be slower, but I don't think you can measure the
difference. "Updates" and "deletes" can be significantly faster with
(logtype,id) rather than (logtype).

The reason is that they are stored as if it were in LIFO order. It means
that if you delete the first record inserted, the server will have to
search through all the (duplicates in the) index entries since it is in the
other end of the queue. Using (logtype,id) makes it unique, and it is
quickly found.

Does this happen to other database? You will either find out about their
internal structures or just try it out.
A few duplicates won't matter, but if you have thousands ...
Check the database statistics, and note the number of duplicates.

> 3) does it always work or does it count on the on entry in the index to
> have a very low selectivity. Is it possible to state a rule of thumb as
> to some radio between the to columns in the index
> 0<logtype<40 and 0 < id <4,000,000 =3D big radio where as 0 < id 10,000
> not so big :-) am I making any sense here?

The number of duplicates in the chain is important. Also, page size (and
correspondingly, the depth of the index tree).


If you search old messages on the IB and Fb newsgroups you will find
explanations (written by Ann Harrison).


Regards,
Aage J.