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/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
