ORDER BY varchar et INDEX

[Paul TOTH]

Bonjour, j'ai un cas de figure que je ne m'explique pas.

Pouvez-vous m'éclairer.

J'ai
- un serveur MySQL 5.1.49 (EasyPHP 5.3.3)
- une base InnoDB contenant entre autres 3 champs varchar (30, 25 et 30) NOM, PRENOM, SURNOM de 172000 enregistrements.
- la base est indexée sur un champ code

si je fais un SELECT * FROM table1 ORDER BY NOM, PRENOM, SURNOM LIMIT 5000, 100 ça prend un temps fou.

EXPLAIN me montre qu'aucun index n'est utilisé et indique un "Using filesort"

si j'ajoute un index
ALTER TABLE table1 ADD INDEX nom (NOM, PRENOM, SURNOM)
j'ai exactement le même résultat !!

j'ai voulu ruser en créant une seconde table contenant un id auto et le champ code de la première et en l'alimentant par
INSERT INTO table2 (CODE) VALUES (SELECT CODE FROM table1 ORDER BY NOM, PRENOM, SURNOM).

avec la requête SELECT * FROM table2 i JOIN table1 t ON t.CODE = i.CODE j'obtiens bien mes enregistrements triés...mais avec des temps de réponse tout aussi catastrophiques et toujours aucun index sur i et un "Using filesort".

Qu'est-ce qui m'échappe dans tout cela ?!

Merci

[Maljuna Kris]

SAluton,
Je ne comprends pas pourquoi tu ne crées pas, simplement, un index sur chacune des trois colonnes NOM, PRENOM et SURNOM

[CinePhil]

Maljuna Kris, il me semble que son idée d'ajouter un index multi-colonnes sur les trois colonnes de l'ORDER BY et dans le même ordre était pourtant une bonne idée et cet index devrait être utilisé non ?

Paul, tu peux nous donner la définition exacte de la table (résultat de SHOW CREATE TABLE la_table), la requête exacte qui prend un temps fou et le résultat de EXPLAIN la_requete ?

[Yanika_bzh]

Est ce que le fait d'enlever * permet de modifier l'explain plan ?

Code :

1
2
3
4
5
6
7
8
SELECT 
  NOM, PRENOM, SURNOM 
FROM 
  table1 
ORDER BY 
  NOM, PRENOM, SURNOM 
LIMIT 
  5000, 100

[Paul TOTH]

Bonjour,

en effet si je ne sélectionne que les 3 champs en question MySQL utilise l'index...mais ça limite beaucoup l’intérêt de la chose ^^

J'ai également 2 champs TEXT qui rallongent manifestement le traitement (tout le reste est du varchar)

En fait le but du jeu est d'identifier des doubles suivant les règles suivantes

- même nom, prenom, surnom
- tous les autres champs sont soit vides, soit égaux
- conserver toutes les valeurs non vides.

exemple:
1, 'PAUL', 'TOTH', '', '42',NULL,'Blahblah'
2,' PAUL', 'TOTH', '', NULL, '974',NULL
3,' PAUL', 'TOTH', '', '42', '974',NULL

sera fusionné (dans une autre table) en un seul enregistrement
1, 'PAUL', 'TOTH', '', '42', '974','Blahblah'

pour cela j'utilise une moulinette PHP qui prend la table triée par nom/prenom/surnom et qui gère la comparaison des champs suivants.

[Yanika_bzh]

Pouvez-vous vérifier l'explain plan avec ce genre de requête :

Code :

1
2
3
4
5
6
SELECT
  Min(CODE), nom, prenom, surnom, colM, colN, colO , Max (COALESCE (colX,'')) AS colX , Max (COALESCE (ColY,'')) AS ColY...
FROM
  Table1
GROUP BY 
  nom, prenom, surnom, colM, colN, colO

Si cela est OK, il ne vous reste plus qu'à insérer vos données dans la table cible.

Bon courage

[Paul TOTH]

Arf ! la même manipulation sur un serveur 4.x fonctionne parfaitement !

Y aurait-il un bug connu sur la 5.1.49-community ?

[Yanika_bzh]

Ou une régression, car un bug similaire était connu dans une version 3 je crois ...