Ajout de champs pour optimiser les requêtes MySQL

Question

J'ai une table MySQL avec 3 champs :

• Localisation
• Variable
• Valeur

J'utilise fréquemment la requête suivante :

SELECT * 
  FROM Table 
 WHERE Location = '$Location' 
   AND Variable = '$Variable' 
ORDER BY Location, Variable

J'ai plus d'un million de lignes dans ma table et les requêtes sont quelque peu lentes. La vitesse des requêtes serait-elle améliorée si j'ajoutais un champ VariableLocation qui est la combinaison de la variable et de l'emplacement ? Je pourrais changer la requête en :

SELECT * 
  FROM Table 
 WHERE VariableLocation = '$Location$Variable' 
ORDER BY VariableLocation

Answer 1

J'ajouterais un index de couverture, pour les colonnes location y variable :

ALTER TABLE 
  ADD INDEX (variable, location);

...mais si les paires variable & lieu sont uniques, elles devraient être la clé primaire.

Combiner les colonnes causera probablement plus de problèmes que cela n'en vaut la peine. Par exemple, si vous avez besoin d'extraire des enregistrements par location o variable seulement, vous devrez soustraire les valeurs dans une sous-requête.

Answer 2

Essayez d'ajouter un index qui couvre les deux champs. Vous devriez alors obtenir un gain de performance tout en gardant vos données compréhensibles car il ne semblerait pas que les deux colonnes doivent être combinées mais vous le faites juste pour obtenir des performances.

Answer 3

Je vous déconseille de combiner les champs. Créez plutôt un index qui couvre les deux champs dans le même ordre que votre clause ORDER BY :

ALTER TABLE tablename ADD INDEX (location, variable);

Les index et les clés combinés ne sont utilisés que dans les requêtes qui impliquent tous les champs de l'index ou un sous-ensemble de ces champs lus de gauche à droite. Ou en d'autres termes : Si vous utilisez l'emplacement dans une condition WHERE, cet index sera utilisé, mais le classement par variable n'utilisera pas l'index.

Lorsque vous essayez d'optimiser des requêtes, la commande EXPLAIN est très utile : EXPLAIN dans la documentation mysql

Answer 4

Mise à jour de la correction :

Courtoisie : @paxdiablo :

Une colonne dans la table ne fera aucune différence. Tout ce dont vous avez besoin est un index sur les deux colonnes et le moteur MySQL l'utilisera. L'ajout d'une colonne dans la table est en fait pire que cela puisqu'il casse la 3NF et gaspille de l'espace. Voir http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html qui stipule : SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; Si un index à colonnes multiples existe sur col1 et col2, les lignes appropriées peuvent être récupérées directement.