Discussion :

[Titum]

Bonsoir à tous,
Je me suis mis au moteur InnoDB de MySQL pour pouvoir gérer les clés étrangères. Je modélise avec Workbench.

Lorsque je crée une clé étrangère dans une table, il créé un index lié à cette clé qu'on ne peut pas enlever.
D'après le site officiel pourtant http://dev.mysql.com/doc/refman/5.6/...eign-keys.html

index_name represents a foreign key ID. The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key

Ce que je comprends comme "s'il y a déjà un index équivalent, il le réutilise au lieu de faire un deuxième index.

Je me retrouve avec des tas d'index, par exemple dans cette table qui lie 2 fois la même colonne de la table "staff" une fois pour la création, une fois pour la modification :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE IF NOT EXISTS `datab`.`tabone` (
  `taboneId` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `name` VARCHAR(150) NOT NULL COMMENT '',
  `staffCreatId` INT(6) UNSIGNED NOT NULL COMMENT '',
  `dateCreation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `staffModifId` INT(6) UNSIGNED NOT NULL COMMENT '',
  `dateModification` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`taboneId`)  COMMENT '',
  INDEX `fk_tabone_staff1_idx` (`staffCreatId` ASC)  COMMENT '',
  INDEX `fk_tabone_staff2_idx` (`staffModifId` ASC)  COMMENT '',
  CONSTRAINT `fk_tabone_staff1`
    FOREIGN KEY (`staffCreatId`)
    REFERENCES `datab`.`staff` (`staffId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tabone_staff2`
    FOREIGN KEY (`staffModifId`)
    REFERENCES `datab`.`staff` (`staffId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Les 2 référencent la même colonne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

REFERENCES `datab`.`staff` (`staffId`)

.
Je comprends que le contenu des 2 colonnes est différent (pas les mêmes staff en création et en modification) mais les indexations sont un peu redondantes à mon goût.
En plus, j'ai ce schéma dans un bon nombre de tables, imaginez donc l'identifiant des utilisateurs indéxé 2 fois dans toutes les tables !

Dans Workbench, si je veux supprimer l'index il m'en empêche et dit :

The index fk_tabone_staff2_idx belongs to the Foreign Key fk_tabone_staff2. You must delete the Foreign Key to delete this index

Donc, la question est : puis-je éviter ces indexations massives ?
Et question subsidiaire si c'est le cas : puis-je le faire dans Workbench ou faut-il le faire à la main dans le SQL de création de base généré ensuite ?

Merci tout plein !

[Artemus24]

Salut titum.

La 'foreign key', c'est déjà un index, donc inutile de créer un 'index' sur les mêmes colonnes que votre 'foreign key'.

La première des choses à faire est d'identifier correctement le nom de vos index et de vos 'foreign key'.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

show create table `tabone`;

Dans votre exemple, vous avez :
--> `fk_tabone_staff1` pour la 'foreign key'
--> `fk_tabone_staff1_idx` pour l'index.
les deux sont liés par mysql.

Vous devez détruire en premier votre 'foreign key', en faisant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter table `tabone` drop FOREIGN KEY `fk_tabone_staff1`;

Maintenant que la 'foreign key' est détruire, vous pouvez détruire votre 'index', en faisant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter table `tabone` drop INDEX       `fk_tabone_staff1_idx`;

Je tiens à préciser que vous devez respecter l'ordre des suppressions. Si vous inversez l'ordre, l'index ne sera pas supprimé.

Éventuellement après, vous pouvez recréer votre 'foreign key' :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter table `tabone` add CONSTRAINT `fk_tabone_staff1` FOREIGN KEY (`staffCreatId`) REFERENCES `datab`.`staff` (`staffId`) ON DELETE NO ACTION ON UPDATE NO ACTION

@+

[Titum]

J'ai fait divers tests et il s'avère que si j'exécute la commande suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE IF NOT EXISTS `datab`.`tabone` (
  `taboneId` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(150) NOT NULL ,
  `staffCreatId` INT(6) NOT NULL ,
  PRIMARY KEY (`taboneId`)  ,
  CONSTRAINT `fk_tabone_staff1`
    FOREIGN KEY (`staffCreatId`)
    REFERENCES `datab`.`staff` (`staffId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

donc sans la ligne créant l'index

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INDEX `fk_tabone_staff1_idx` (`staffCreatId` ASC)

MySQL crée lui-même un index dans la table.

Si je crée la table sans clé et que je l'ajoute par la ligne d'altération que tu propose, il se passe la même chose.

Et si je veux retirer l'index, MySQL répond "error 1533 : Cannot drop index 'fk_tabone_staff1_idx': needed in a foreign key constraint".

J'en conclue par ces tests qu'il n'y a rien à faire, MySQL nécessite obligatoirement 1 index pour chaque clé étrangère ! Si on ne lui donne pas, il le crée.

Je me dis alors que je pourrais faire une table qui rassemble ces données pour toutes les autres tables, genre avec les colonnes tableNom,IdLigneDansLaTable,staffCreatId,staffModifId mais ça va être galère en maintenance.
Je me trouve donc contraint d'avoir ces tas d'index partout je crois.

[Artemus24]

Salut titum.

Attention : Je fais la distinction entre la 'primary key', les 'foreign key' et les 'index', même si au final, ce sont tous des index.

MySQL crée lui-même un index dans la table.

Je ne comprends pas bien ce que tu veux faire.
Si tu as besoin d'une 'Foreign Key', MySql va te créer une contrainte sur cette clef étrangère. Le résultat sera un index que tu peux consulter en allant sous PhpMyAdmin.
C'est ainsi que MySql va faire le lien entre la table fils et la table père. Donc, tu ne peux pas supprimer ce 'Foreign Key' si tu en as besoin.
Les deux contraintes sur des clefs étrangères impliquent la création de deux index sur ces 'foreign key'. C'est obligatoire !

Maintenant, quel est l'utilité de ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INDEX `fk_tabone_staff1_idx` (`staffCreatId` ASC)

Pour moi, cela fait un double emploi avec ta 'foreign key'. As-tu fait un 'explain' sur une requête qui demande de l'optimisation ?

Si je crée la table sans clé et que je l'ajoute par la ligne d'altération que tu proposes, il se passe la même chose.

Oui, c'est normal. Ce sont deux écritures différentes qui font exactement la même chose.

Dans ta façon de t'exprimer, il y a comme une ambiguïté au sujet de 'sans clef'.
Je suppose que tu voulais dire sans 'foreign key' et que tu l'as ajouté, par la suite, dans le 'alter table'.
Et que tu ne parlais pas de la 'primary key'.

Et si je veux retirer l'index, MySQL répond "error 1533 : Cannot drop index 'fk_tabone_staff1_idx': needed in a foreign key constraint".

Oui, je sais que tu auras ce message d'erreur. Il faut d'abord supprimer le 'foreign key' avant de pourvoir supprimer l'index, sinon ça ne fonctionne pas.
Je t'ai indiqué ceci dans mon précédent message.

J'en conclue par ces tests qu'il n'y a rien à faire, MySQL nécessite obligatoirement 1 index pour chaque clé étrangère !

Si tu demandes une 'foreign key', MySql va te créer un index pour cet clef étrangère. Tu ne peux pas faire autrement.
Maintenant, la bonne question est de savoir si tu as besoin de l'index que tu crées sur la même colonne et qui fait double emploi ?

Si on ne lui donne pas, il le crée.

Il crée quoi ? Et qu'est-ce que tu ne lui donnes pas ?

Je me trouve donc contraint d'avoir ces tas d'index partout je crois.

Le minimum dans ta table, ce sont la 'primary key' et les contraintes 'foreign key', soit trois index de nom 'primary', 'fk_tabone_staff1' et 'fk_tabone_staff2'.
Les autres index sont justes là pour des questions de performances.

Je ne sais pas répondre à cette question sans voir la globalité des requêtes sur cette table. Et vu que je ne connais pas la volumétrie de ta table, il m'est difficile de te conseiller correctement.

Si la volumétrie est très faible, tu peux te permettre de ne pas ajouter d'autres index.
L'inconvénient des index se porte essentiellement sur les insertions ou ceux-ci prennent plus de temps à l'exécution.
Par contre, cela améliore grandement les performances en lecture.

Donc, je ne comprends pas trop le problème que tu as avec tes 'foreign key' et tes index.

@+

[Titum]

Ma question que je tente de simplifier c'est :
Primary key, index et foreign key sont trois types d'index.
- Si j'ajoute une primary key on a bien 1 index de créé
- Si on ajoute un index sur une colonne, on a bien 1 index de créé.
- Si on ajoute une foreign key, on a bien la clé étrangère qui s'ajoute ET 1 index EN PLUS, même si on ne fait que demander CONSTRAINT `fk_nom` FOREIGN KEY (`col_nom`) REFERENCES `db`.`tab` (`col`)

Cet index créé sans qu'on le demande en même temps qu'une foreign key est-il vraiment nécessaire au fonctionnement de la foreign key ?

[Artemus24]

Salut Titum.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
PRIMARY KEY (`taboneId`)  COMMENT '',
  INDEX `fk_tabone_staff1_idx` (`staffCreatId` ASC)  COMMENT '',
  INDEX `fk_tabone_staff2_idx` (`staffModifId` ASC)  COMMENT '',
  CONSTRAINT `fk_tabone_staff1` FOREIGN KEY (`staffCreatId`) REFERENCES `datab`.`staff` (`staffId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tabone_staff2` FOREIGN KEY (`staffModifId`) REFERENCES `datab`.`staff` (`staffId`) ON DELETE NO ACTION ON UPDATE NO ACTION)

- Si j'ajoute une primary key on a bien 1 index de créé

Oui, il y a bien un index de nom 'PRIMARY'.

- Si on ajoute un index sur une colonne, on a bien 1 index de créé.

Oui, il y a bien un index sur la ou les colonnes dont le nom sera celui que vous avez précisé dans votre déclarative.
Dans votre exemple, il s'agit de `fk_tabone_staff1_idx` et de `fk_tabone_staff_idx`.

Je suppose que ce sont bien ces deux index qui vous posent des problèmes de compréhension. Est-ce vous qui les avez ajoutés ?
Normalement, ils ne sont pas obligatoire pour faire fonctionner vos deux clefs étrangères.

- Si on ajoute une foreign key, on a bien la clé étrangère qui s'ajoute ET 1 index EN PLUS, même si on ne fait que demander CONSTRAINT `fk_nom` FOREIGN KEY (`col_nom`) REFERENCES `db`.`tab` (`col`)

Oui, il y a bien un index pour la foreign key et il est unique pour cette foreign key.

En reprenant votre exemple, vous avez :
--> 1 index sur la primary key.
--> 2 index non unique, de nom `fk_tabone_staff1_idx` et `fk_tabone_staff2_idx`
--> 2 index pour vos deux foreign key, de nom `fk_tabone_staff1` et `fk_tabone_staff`.
Cela fait cinq index.

Cet index créé sans qu'on le demande en même temps qu'une foreign key est-il vraiment nécessaire au fonctionnement de la foreign key ?

Pour le fonctionnement de vos deux clefs étrangères, vous avez besoin uniquement de ceux qui se nomment `fk_tabone_staff1` et `fk_tabone_staff`.
Si vous parlez de `fk_tabone_staff1_idx` et `fk_tabone_staff2_idx, ils ne servent à rien pour le fonctionnement de votre table.

Mon hypothèse est qu'un DBA à créé ces deux index pour des raisons de performances.

@+

[Titum]

J'ai mis un bon moment avant de reprendre ce projet, j'arrive n peu longtemps après ces réponses.

Donc en effet, ces index ne sont pas obligatoire pour le fonctionnement de la clé primaire.
Je vais voir s'il reste quand même utiles pour certains requêtes.

Merci beaucoup