At 10:00 AM 19-11-01 +0000, Tom Deprez wrote:

>Hi,
>
>Is it always better to use a generated field as a primary key?.

Generally, yes. Primary keys should be atomic (have no meaning except to form the key) and they should be short. A simple primary key is also makes foreign key relationships much simpler and index sorting and matching faster.

>Let say that in a table you can create a primary key with the first 3
>or 4 fields, will this bring a speed reduction?

No, it will bring a speed increase because of size and add a lot of complexity and overload to joins. It is also possible to clue up with an impossible index, since index lengths are limited to 253 bytes.

>Or is it just by experience that you better can use seperate (auto
>generated) primary keys. I've read books/articles and one says you
>need to use a generated key and another one says that if you can
>avoid a generated key, you need that one...

Generators make fantastically marvellous keys. Use them wherever you can. It's OK to use smaller keys than Int64 on small, static sets such as control tables, where you might as well use smallint or char() keys of 1 to 3 characters, e.g. in a table of transaction types.

Some relational databases - such as Paradox - don't support FOREIGN key relationships and rely on composite primary keys to form hierarchical relationships. Such is not the case with Firebird/InterBase, whose generator gives you a bomb-proof primary key and whose relationships can be made with gossamer-fine key linkages.

>Creating indexes:
>
>if you create an index. no sorting order, will it than be slower than
>the same key with a descending or ascending order?

All indexes have a sorting order. The default order is ASC(ending). You only need to specify the sorting order if you want a DESC(ending) index.

>So, if I need a descending and a descending order key for that field,
>then I create 2 keys (desc, asc) or 1 key with no order.

Don't confuse keys with indexes. We are talking about indexes here, not keys. A key is a constraint, a pure and abstract thing; an index is a live and writhing animal with all the concommitant bodily functions.

A column which is a key (primary or foreign) will have an ascending index created for it automatically. Don't create another ascending index for it, otherwise you will kill the optimizer. Create a descending index later, if you need one, but wait until you have finished creating all the FOREIGN keys that refer to that column or you may well end up back here in this list complaining about all the hair you have lost.

>Is Firebird smart enough to use a key or do you always have to
>explecit say that it has to use that key.

Your question should be "Is Firebird smart enough to use an index?" Yes, Firebird is usually smart enough to use an index if it will be faster than using natural order. But don't make it hard for the optimizer by creating redundant indexes on primary and foreign key columns.

Also, watch out for index selectivity. Don't put foreign keys or indexes on columns where, in a table of thousands of rows, there are only a few possible values. Instead, make a composite index on that column + the primary key, in that order.

For example, in an invoice detail table you might have a column TRANSACTION_TYPE. There are only 5 possible transaction types, so don't make a foreign key on this column. (If you do, Firebird will create an index on it and this index over time will form huge duplicate chains and slow your invoice operations to a death march).

Instead, if you need, for instance, to improve the performance of a query that is sorted by transaction type, form a highly selective index on this column: pick up the primary key column (call it INV_DETAIL_ID) and form an index by adding it to the right-hand side of TRANSACTION_TYPE, e.g.

create index IX_TRANS_TYPE on InvoiceDetail(TRANSACTION_TYPE, INV_DETAIL_ID);

cheers,
Helen

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