Discussion :

[FMaz]

Je suis en train d'essayer de résoudre une requête problématique, alors je l'analyse avec EXPLAIN:

Code :

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

1
2
3
4
5
6
7
8
 
mysql> EXPLAIN SELECT * FROM cc_he_fromto WHERE persoid=325;
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | cc_he_fromto | ALL  | PRIMARY       | NULL | NULL    | NULL | 108421 | Using where | 
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

Le problème c'est le Type= ALL, qui fait en sorte que ma requête met un temps fou à être exécutée. La table est assez grosse, et je dois absolument éviter les FULL TABLE SCAN.

Voici ma table:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
mysql> SHOW COLUMNS FROM cc_he_fromto;
+-------------+-----------------------+------+-----+---------+-------+
| Field       | Type                  | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+-------+
| msgid       | int(12) unsigned      | NO   | PRI | 0       |       | 
| fromto      | enum('from','to')     | NO   | PRI | from    |       | 
| persoid     | varchar(12)           | NO   | PRI | 0       |       | 
| lieuid      | mediumint(5) unsigned | NO   |     | 0       |       | 
| masque      | tinyint(1)            | NO   |     | 0       |       | 
| description | text                  | NO   |     | NULL    |       | 
| show        | tinyint(1)            | NO   | PRI | 1       |       | 
+-------------+-----------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

Du coté de mes index, ca ressemble à ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
mysql> SHOW INDEX FROM cc_he_fromto;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| cc_he_fromto |          0 | PRIMARY  |            1 | persoid     | A         |          30 |     NULL | NULL   |      | BTREE      |         | 
| cc_he_fromto |          0 | PRIMARY  |            2 | show        | A         |          30 |     NULL | NULL   |      | BTREE      |         | 
| cc_he_fromto |          0 | PRIMARY  |            3 | msgid       | A         |      185357 |     NULL | NULL   |      | BTREE      |         | 
| cc_he_fromto |          0 | PRIMARY  |            4 | fromto      | A         |      185357 |     NULL | NULL   |      | BTREE      |         | 
| cc_he_fromto |          1 | msgid    |            1 | msgid       | A         |      185357 |     NULL | NULL   |      | BTREE      |         | 
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.18 sec)

Enfin, là ou je ne comprend plus rien, c'est que si je sélectionne des champs indexé, le type ALL change pour type INDEX:

Code :

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

1
2
3
4
5
6
7
8
 
mysql> EXPLAIN SELECT msgid, fromto, persoid, `show`  FROM cc_he_fromto WHERE persoid=325;
+----+-------------+--------------+-------+---------------+-------+---------+------+--------+--------------------------+
| id | select_type | table        | type  | possible_keys | key   | key_len | ref  | rows   | Extra                    |
+----+-------------+--------------+-------+---------------+-------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | cc_he_fromto | index | PRIMARY       | msgid | 4       | NULL | 185361 | Using where; Using index | 
+----+-------------+--------------+-------+---------------+-------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

Mais si j'ajoute "masque", "lieuid" ou "description", je retombe à type ALL. Meme que si je met seulement un de ces 3 champs, je retombe à type ALL:

Code :

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

1
2
3
4
5
6
7
8
 
mysql> EXPLAIN SELECT lieuid  FROM cc_he_fromto WHERE persoid=325;
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | cc_he_fromto | ALL  | PRIMARY       | NULL | NULL    | NULL | 185322 | Using where | 
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Quelqu'un pourrait m'éclairer un peu sur la raison de ce comportement ? Pourquoi MySQL se pré-occupe des champs à sélectionner ?


Merci !


Note:
Je suis conscient que SHOW est un mot clé réservé, et il est toujours entre `` dans mes requêtes.

[CinePhil]

Pourquoi as-tu une clé primaire multiple alors qu'il semble que la colonne msgid soit suffisante ?

La colonne persoid fait partie de la clé primaire mais comme ce n'est pas la première colonne de la clé, elle n'est pas indexée individuellement donc MySQL ne sait pas utiliser la clé primaire pour faire une recherche sur cette colonne.

[FMaz]

Hum, oui, j'ai réellement besoin de cette clé multiple, l'explication sera plutot longue car c'est un système de messagerie particulier et assez complexe dans le cadre d'un jeu de rôle en ligne... Donc ca dépasse le cadre classique d'une messagerie ordinaire disons.

Je ne comprend pas pourquoi vous dite que persoid n'est pas la première colone de la clé primaire multiple, il me semble bien que oui. C'est ce que je vois partout en tout cas:

mysql> SHOW INDEX FROM cc_he_fromto;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| cc_he_fromto | 0 | PRIMARY | 1 | persoid | A | 30 | NULL | NULL | | BTREE | |
| cc_he_fromto | 0 | PRIMARY | 2 | SHOW | A | 30 | NULL | NULL | | BTREE | |
| cc_he_fromto | 0 | PRIMARY | 3 | msgid | A | 185357 | NULL | NULL | | BTREE | |
| cc_he_fromto | 0 | PRIMARY | 4 | fromto | A | 185357 | NULL | NULL | | BTREE | |
| cc_he_fromto | 1 | msgid | 1 | msgid | A | 185357 | NULL | NULL | | BTREE | |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows IN SET (0.18 sec)

En ce qui concerne la raison pourquoi msgid ne suffit pas, celà me semble évident si l'on regarde la requête effectuée ici: c'est persoid qui est utilisé, et non msgid

[CinePhil]

Je ne comprend pas pourquoi vous dite que persoid n'est pas la première colone de la clé primaire multiple, il me semble bien que oui. C'est ce que je vois partout en tout cas:

Au temps pour moi ! J'avais regardé le tableau SHOW COLUMNS qui avait mis en premier msgid et pas celui des index.

Je viens de voir un truc : Dans la requête, la valeur cherchée de persoid est un entier, ce qui semble normal, mais le type de la colonne persoid est un VARCHAR(12), ce qui n'est pas un bon choix !

[FMaz]

Mais est-ce que celà explique le type all ?

Dans ce cas-ci, persoid est un entier. Cependant, si un message a été envoyé par le système, on y verra "System", par un maitre de jeu: "MJ", ou n'importe quelle autre valeur personnalisé en fonction des besoin de ce dernier.

Je pourrais, certe, faire un champs 'customName', et l'utiliser lorsque persoid=0.

Mais est-ce que cela résoudrait réellement le problème actuel ?

[StephaneC.]

Ton problème est vraiment représentatif des subtilités de l'indexation ! Je vais essayer de répondre à tes 2 questions : 1- pourquoi le type ALL quand tu fais un SELECT * et 2- pourquoi un type INDEX quand tu mets certaines colonnes ?

1- l'optimiseur de requêtes voit que ta clause WHERE porte sur une colonne sur laquelle existe un index utilisable : il s'agit de la 1ère colonne de la clé primaire. Donc la clé primaire fait partie des index candidats pour résoudre la requête de manière optimale. C'est bien ce qui apparaît dans la colonne possible_keys de EXPLAIN. Sauf que ...
MySQL n'est pas capable d'utiliser un index sur une colonne si la colonne fait partie d'une expression ou se trouve dans une fonction. Or la colonne persoid étant déclarée en VARCHAR et 325 étant un entier, MySQL doit faire une conversion, ce qui est assimilable à l'appel d'une fonction. Au bout du compte, l'index candidat ne peut pas être utilisé (KEY : NULL) et comme il n'existe pas d'autre index possible, la seule façon d'exécuter la requête est de parcourir toute la table (type : ALL).

2- dans ta 2è requête, tu tombes dans un cas très particulier appelé index couvrant : les colonnes de ton SELECT et de ta clause WHERE sont toutes couvertes par la clé primaire, ce qui signifie qu'en lisant les valeurs de l'index, MySQL dispose de toutes les informations que tu lui as demandées. Dans ce cas, il n'est pas utile d'aller lire les autres colonnes de la table. Et comme il est plus rapide de lire l'index que les données, l'optimiseur choisit de faire un scan complet de l'index (type : INDEX) plutôt qu'un scan complet de la table. L'info comme quoi tu utilises un index couvrant se trouve dans la colonne Extra (Using index).
Dès que tu mets d'autres colonnes que celles de ta clé primaire, l'index n'est plus couvrant et la seule solution est de revenir au scan de la table.

Désolé c'était un peu long mais tes questions méritaient vraiment une petite explication !

Stéphane

[CinePhil]

Super explication ! Bravo !

[FMaz]

Woa, je repasse un peu tard, mais sérieusement, j'ai vraiment un reproche à t'adresser que je trouve complètement inacceptable dans la réponse:

t'excuser d'avoir été un peu long.

C'est exactement le genre d'explication que je voulais, et chaque détail à sa place et sa pertinence.

Merci beaucoup pour cette réponse (que je n'aurais jamais trouvé seul).