ok, thanks for your responses !
> -----Message d'origine----- > De : Zabach, Elke [mailto:[EMAIL PROTECTED] > Envoyé : lundi 16 janvier 2006 12:57 > À : Fabrice Bourdel; maxdb > Objet : AW: Simple optimization question with index necessary or not > > > 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]
