From: Zabach, Elke (elke.zabachsap.com)
Date: Mon Jan 16 2006 - 05:56:35 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Fabrice Bourdel wrote:

> Hi,
>
>
> I have an optimization question. Assuming this script :
> //
> create domain D_LONG fixed(10)
> //
> create domain D_DATETIME timestamp
> //
> create domain D_DATETIME_DEFAULT timestamp default timestamp
> //
> create table SOCIETE_REF_ORGANISME(
> SORE_ID D_LONG, -- |PK|
> OR_ID D_LONG, -- |PK|
> SOREOR_DTCRE D_DATETIME_DEFAULT, -- Date/Heure de création
> SOREOR_DTMOD D_DATETIME, -- Date/Heure de modification
> primary key(SORE_ID, OR_ID),
> foreign key FK_SOREOR_SORE (SORE_ID) references SOCIETE_REF,
> foreign key FK_SOREOR_OR (OR_ID) references ORGANISME
> )
>
> (considering table ORGANISME & SOCIETE_REF exists)
>
> Is it better to have an index on SORE_ID (because SORE_ID is already in
> the primary key index) with :
> //
> create index IND_SOREOR_FK_SORE_ID on SOCIETE_REF_ORGANISME (SORE_ID)
>
>
> Is it also better to have an index on OR_ID (because OR_ID is at 2nd
> position in the key)
> //
> create index IND_SOREOR_FK_OR_ID on SOCIETE_REF_ORGANISME (OR_ID)
>
>
> I would say index on OR_ID is good, but on SORE_ID not needed.
> Am i right ?
>
>
I agree, because finding (for foreign-key-check) of corresponding sore_id will be done using a key-strategy.

I agree, too, with you OR-ID-opinion (having an index would be good) as it does not depend on the USER-given queries for this table but on the implicit checks needed for checking the foreign key if OR_ID in ORGANISME will be updated/deleted. Ok, the organization will not be changed every day several times, therefore, in this special case the index is not really needed (those 1-3 updates/deletes in the organization may last a little bit longer without index, but all insert/update/delete in SOCIETE_REF_ORGANISME will not need to handle that index).
Therefore: usually an index is good for such column, in your case it is not needed.

But please pay attention to tables SOCIETE_REF and ORGANISME and sore_id / or_id in these tables which should be keycolumn (I assume they are keycolumns) or at least index_column.

Elke
SAP Labs Berlin

> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe: http://lists.mysql.com/maxdb?unsub=elke.zabachsap.com

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]