[MySQL] Erreur dans le choix d'un index pour une requête SELECT

Bonjour à tous, j'ai un soucis avec ma BD, je vais essayer d'exposer le plus clairement possible mon problème, étant donné que je ne suis pas un expert :) .

Dans ma BD, j'ai une table,

Code:

---

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE T_MA_TABLE ( SPECT_ID INTEGER NOT NULL, SPECT_MEASURE_DATE TIMESTAMP NOT NULL, SPECT_ANSWER_TYPE TINYINT(1) NOT NULL, S_CODE VARCHAR(15) NULL, B_NAME VARCHAR(40) NULL, X_NAME VARCHAR(40) NULL, CHAIN_NAME VARCHAR(40) NULL SITE_NAME VARCHAR(40) ) TYPE=INNODB;

---

avec comme PK SPECT_ID en auto-increment.

J'ai plusieurs indexs sur cette table et notamment les 2 suivants :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE INDEX IND_SPECT_X_DATE ON T_MA_TABLE ( S_CODE , X_NAME , SPECT_MEASURE_DATE   );   CREATE INDEX IND_SPECT_B_DATE ON T_MA_TABLE ( S_CODE , B_NAME , SPECT_MEASURE_DATE );

---

Maintenant, j'exécute cette requête :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT * FROM maBD.t_ma_table t WHERE X_NAME = "TOTO" AND SPECT_ANSWER_TYPE = "0" AND S_CODE = "TATA" AND SITE_NAME LIKE "%" AND CHAIN_NAME LIKE "%" AND SPECT_TYPE LIKE "%" ORDER BY SPECT_MEASURE_DATE;

---

Cette requête me retourne plus ou moins 1500 lignes en qqs secondes.

Maintenant si j'exécute cette requête :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT * FROM maBD.t_ma_table t WHERE X_NAME = "TITI" AND SPECT_ANSWER_TYPE = "0" AND S_CODE = "TUTU" AND SITE_NAME LIKE "%" AND CHAIN_NAME LIKE "%" AND SPECT_TYPE LIKE "%" ORDER BY SPECT_MEASURE_DATE;

---

Cette requête me retourne plus ou moins 1500 lignes, MAIS en 10 minutes...

Alors ne comprenant pas, j'ai exécuté ces 2 requêtes avec "EXPLAIN" devant pour voir ce qu'il se passait .

-> Pour la 1ère requête, MySQL utilise le bon index (le 1er) puisque je renseigne le champ X_NAME.
-> Pour la 2nde requête MySQL n'utilise pas le bon index (le 2nd)... Alors que seuls les 2 paramètres X_NAME et S_CODE changent...

Sachant que ces requêtes sont générées par mon programme java, et que donc je ne peux faire un truc du style USE_INDEX mon_bon_index, que me conseilleriez-vous de faire pour rétablir la situation??

D'avance merci pour vos tuyaux :)

[Edit]

J'ai trouvé un contournement : dans la seconde requête, celle qui prend le mauvais index je rajoute un "AND B_NAME IS NULL", ce qui force mySQL à utiliser le bon index.

Cela dit, si qq'un a des éclaircissements la dessus, il sera le bienvenu :D

N'as-tu pas fait une erreur dans le code que tu as copié dans ton post pour la 2è requête ?
Parce qu'en l'état, je ne vois pas pourquoi la 2è requête devrait utiliser le 2è index.

Stéphane

Votre requêtes est une horreur, que dis-je, un tissus d'âneries :
1) les chaines de caractères s'expriment entourées d'apostrophe et non de guillemets. (6 erreurs danc chacune de vos requêtes).
2) l'utilisation du "SELECT *" est contre performant. Si vous voulez de la performance, commencer par nommer explicitement les colonnes qui doivent être retournées et évitez de retourner celles qui n'ont pas besoin d'être affichées
3) L'utilisation du LIKE '%' est une stupidité qui force le moteur à rechercher n'importe quoi... C'est à dire à le faire travailler pour rien.
4) SQL est un langage fortement typé. En faisant SPECT_ANSWER_TYPE = '0' alors que cette colonne est un entier vous obligez les entiers de cette colonne à être convertis en chaine de caractères ce qui empêche toute indexation.

Au final votre première requête devrait être :

Code:

---

1 2 3 4 5 6

SELECT SPECT_ID, ... FROM maBD.t_ma_table t WHERE X_NAME = 'TOTO' AND SPECT_ANSWER_TYPE = 0 AND S_CODE = 'TATA' ORDER BY SPECT_MEASURE_DATE;

---

Bref, commencez par apprendre le SQL. C'est un VRAI langage et non pas la bouillie informe que vous tentez d'appliquer. Mon site, comme mes bouquins peuvent vous y aider !

La pose d'index sur de telles requête ne servira à rien !


A +

Citation:

---

Bref, commencez par apprendre le SQL

---

Soit, sauf que comme je l'ai dit dans mon Post, c'est mon programme Java qui génère cette requête...
Pas besoin d'être aussi remonté...

C'est bien le problème !!!! Vous ne pourrez jamais optimiser une requête si elle n'est pas construite correctement dès le départ. Ne rêvez pas !!!!!!!!!

A +

Hmmm je me doute bien, seulement je me demandais si une solution pouvait s'appliquer ici, étant donné que les 2 requête sont strictement les mêmes et que seuls 2 valeurs de paramètres diffèrent !

le seul moyen d'optimiser est de cesser d'utiliser des produits stupide qui font de la merde en terme de SQL et contre lequel toute optimisation est impossible.

Je ne cesse de dénoncer cela et voit de plus en plus ce genre d'horreur dans les audits de bases de données que je fais !

A +

Citation:

---

Envoyé par GyZmoO

Hmmm je me doute bien, seulement je me demandais si une solution pouvait s'appliquer ici, étant donné que les 2 requête sont strictement les mêmes et que seuls 2 valeurs de paramètres diffèrent !

---

Le serveur dispose de statistiques sur la répartition des valeurs indexées, même si je ne sais pas avec quel niveau de détail. Il est donc possible que des valeurs différentes change les choix du plan d'exécution. Un ANALYSE TABLE permet de réviser ces statistiques et donc pourrait mener à un choix plus raisonnable.