2006/1/13, Fabrice Bourdel <[EMAIL PROTECTED]>: > 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 on the SORE_ID. Whether you need the OR_ID index really depends on the queries you're doing. Chances are that you need it but it depends. If the table is not too big (i.e. index creation is not costly) you can create the index and check execution plans of your favourite SQL (TM). HTH robert -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
