Should I index a relation..

[LinuXelite]

Hi!

I have to table...

CREATE TABLE if not exists clients (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
NOCLIENT CHAR(12),
PRIMARY KEY(id), UNIQUE(id, NOCLIENT), INDEX(id, NOCLIENT)
) TYPE=MyISAM;

and

(FACUTRE = INVOICE)
CREATE TABLE if not exists factures (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
C_F CHAR(1),
idclient SMALLINT UNSIGNED,
idvendeur SMALLINT UNSIGNED,
idterritoire SMALLINT UNSIGNED,
idbapteme SMALLINT UNSIGNED,
PRIMARY KEY(id), UNIQUE(id, COMMANDE_R), INDEX(id, COMMANDE_R, FACTURE_R, DATE_R, idclient)
) TYPE=MyISAM;


The relation is between client.id and facture.idclient.
This table has 1 gig of data (raw text in a cobol file). I transfert it into mysql. To have FAST RESULT when I group by client, should I index facture.idclient ???

What do u think

 

[cluelessNewbie]

the trade off is speed of insert vs. speed of access. If these are build-once kinda tables then I'd definely go for the extra index. If you're adding & deleting records all day then you're going to have to do the math.

Because most of my tables are large & stable I tend to index a lot of the fields... your milage may vary tho.