At 02:54 AM 17-06-01 +0200, you wrote:

>Hi,
>
>I am wondering how many records a table must contain so that
>indexing does not slow down selects.

Good indexing doesn't slow down selects, it speeds them up.

>I have some very small tables with 25 up to 75 records which
>usually are not changed very often.
>I heard when using very small tables, indexes are not needed
>because they produce too much overhead.
>
>Perhaps some of you have already made some experiences to which
>treshold (approx. no of records) it is useful not to use indexes?

Indexes on small ("control") tables won't slow anything down, since the tables are usually pretty static anyway and you are not going to be performing huge bulk inserts or deletes to them. Some would say that it's not worth indexing them but you are using IBO, which makes good use of indexes.

Making foreign keys on other tables that reference these small tables will DEFINITELY slow things down - IB will form long duplicate chains in the index tree of the mandatory index and wreck performance. Instead, make composite indexes on such columns, combining the lookup column + the primary key.

Whilst it's true that there is "overhead" in maintaining indexes, there is much more overhead in trying to perform queries without indexing on the columns involved in joins and sorts!

Don't index every column, for sure! And be very careful not to create indexes that already exist, e.g. adding a unique ASC index to the primary key column or a non-unique one to a foreign key column. These already exist and, if there are two, it confuses the optimizer and it won't use either index in its plan.

My rule is - if I need to index a column for joining or sorting purposes, I do it, and know that it will give me better performance.

You can always deactivate indexes for bulk inserts...

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________