Discussion :

[ctobini]

Bonjour,

J'utilise MySQL 5.0 sous Linux et j'ai une table A et une table B.

La table B est l'insertion d'un fichier (LOAD DATA LOCAL INFILE) ayant beaucoup de colonnes en commun avec la table A.

Je souhaite croiser les données entre les 2 tables avec un JOIN.

Mon problème est qu'aucune clé étrangère ne lie la table B à la table A, vu que c'est une injection de fichier, la requête n'est pas efficace du tout

Sauriez-vous s'il y a moyen d'optimiser les requêtes dans ce cas de figure ?

En vous remerciant,

C. Tobini

[Sivrît]

Il faudrait voir plus en détail ce que "croiser" signifie, mais à l'instinct l'équation suivante me vient :
"JOIN" + "lent" = "index manquant"

[ctobini]

Bonjour et merci de ta réponse,

Pour schématiser :

table A:
ida INT AUTO_INCREMENT PK,
nb1 INT

table B:
nb2 INT

Dans la réalité ces tables ont plusieurs colonnes en commun, je les détaillerai plus tard si besoin est.

J'ai lu sur la doc MySQL que pour des tables InnoDB (c'est mon cas) un index n'est utile dans le cas d'un JOIN que si une clé étrangère existe. Là ce n'est pas mon cas du tout puisque la table B résulte de l'injection d'un fichier.

Ma question serait: si je crée un INDEX pour nb1 et nb2 (en fait toutes les colonnes permettant de vérifier l'égalité), est-il susceptible d'accélérer la requête JOIN ?

Le cas échéant, si une clé étrangère est requise, je pensais à faire (toujours après avoir créé un INDEX pour nb1 et nb2) un :

Code :

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

1
2
 
SELECT ida FROM table A, table B WHERE nb1 = nb2;

En fait je voudrais savoir si, lorsqu'on est certain de requêter une colonne, il est judicieux de l'indexer ? Il me semble avoir lu qu'ajouter des index pouvait même ralentir certaines requêtes si ceux-ci ne sont pas judicieusement choisis.

Ex :

table A :
ida PK,
nb1 INT

L'index ida PK permet d'accélérer la recherche dans le cas d'un :

Code :

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

1
2
 
SELECT * FROM table A WHERE nb1 = x;

indexer nb1 n'est pas forcément utile.

Bref, je suis un peu paumé sur la gestion des index et la possibilité de croiser des tables, tout le monde l'aura compris

C. Tobini

[Sivrît]

J'ai lu sur la doc MySQL que pour des tables InnoDB (c'est mon cas) un index n'est utile dans le cas d'un JOIN que si une clé étrangère existe. Là ce n'est pas mon cas du tout puisque la table B résulte de l'injection d'un fichier.

Ce serait plutôt qu'un index n'est indispensable que si il y a une contrainte de clef étrangère, et les dernières versions vont de toute façon le créer automatiquement.

Ma question serait: si je crée un INDEX pour nb1 et nb2 (en fait toutes les colonnes permettant de vérifier l'égalité), est-il susceptible d'accélérer la requête JOIN ?

Oui tout à fait. En notant qu'un seul index ne peut être utilisé pour une jointure, donc dans ce cas l'index sur nb1 ou celui sur nb2 sera utilisé, mais pas les deux. Donc on peut se contenter d'un des deux. Le plus intéressant dépendra probablement des tailles respectives des tables mais ce sera de toute façon bénéfique.
De plus, s'il y a plusieurs colonnes, des indexes sur a, b et c ne sont pas substituables à un index sur a, b et c.

Le cas échéant, si une clé étrangère est requise, je pensais à faire (toujours après avoir créé un INDEX pour nb1 et nb2) un :

Code :

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

1
2
 
SELECT ida FROM table A, table B WHERE nb1 = nb2;

C'est typiquement une requête qui bénificie grandement d'un index.

En fait je voudrais savoir si, lorsqu'on est certain de requêter une colonne, il est judicieux de l'indexer ? Il me semble avoir lu qu'ajouter des index pouvait même ralentir certaines requêtes si ceux-ci ne sont pas judicieusement choisis.

En règle générale oui.
Mais il faut que l'index soit utilisé (forcément) et mysql peut décider de ne pas le faire dans certains cas, par exemple s'il n'est pas assez discriminant : si la colonne indexées prend comme valeur vrai ou faux il pourrait être plus efficace d'ignorer l'index.
En outre un index a un cout. Il prend de la place sur le disque, est en compétition avec les autres indexes pour être conservé en mémoire et doit être tenu à jour donc va ralentir (un petit peu) les ajouts/modifications/retraits sur la table. En contrepartie il accélère énormément les recherches, et ce d'autant plus que la table grossi.

Après c'est une question de priorité et d'estimation. Gagner 10 minutes sur une requête lancée tous les mois la nuit ne vaut peut-être pas de ralentir des insertions dans la table qui constituent la charge la plus critique de la BDD.

Mais dans l"ensemble indexer c'est bien

Ex :

table A :
ida PK,
nb1 INT

L'index ida PK permet d'accélérer la recherche dans le cas d'un :

Code :

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

1
2
 
SELECT * FROM table A WHERE nb1 = x;

indexer nb1 n'est pas forcément utile.

Alors en fait... si.
Cette requête recherche tous les enregistrements de A pour lesquels nb1 a une valeur particulière, ce qui est précisément le but d'un index.

Un index est une liste ordonnée des valeurs indexées (plus un moyen de retrouver pour chaque valeur la ou les enregistrements eux-mêmes), ce qui permet de les y retrouver rapidement. Le gain par rapport à un parcourt de la table est d'autant plus important qu'il y a beaucoup d'enregistrements dans la table.

[ctobini]

Bonjour et meilleurs voeux 2008 !

Désolé de la réponse très tardive, vacances obligent...

Je ne comprends pas ce que vous voulez dire par :

De plus, s'il y a plusieurs colonnes, des indexes sur a, b et c ne sont pas substituables à un index sur a, b et c.

Auriez-vous un exemple rapide pour ce cas de figure ?

Je suis dans un cas de figure ou plusieurs tables ont des colonnes en commun mais pas de clés étrangères, j'avais dans l'idée de créer un index pour chaque colonne commune avec la colonne d'une autre table.

En vous remerciant,

C. Tobini