Lenteur / optimisation requête

[joannesdekoster]

Bonjour,

La requête suivante met plus de 5 secondes à aboutir, ce qui me semble très long :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT user_infos.id_infos, game_avatar.id_avatar, 
  game_avatar.pseudo AS avatar, 
  user_infos.pseudo AS compte, 
  _adm_user.login AS email, 
  DATE_FORMAT(FROM_UNIXTIME(game_avatar.last_connexion), '%d-%m-%Y') AS derniere_connexion, 
  IF(game_avatar.etat_avatar = 1, 'non', 'oui') AS verifie 
FROM game_avatar, user_infos, user_infos_adm_user, _adm_user 
WHERE (
  (game_avatar.id_user = user_infos.id_infos) 
  AND (user_infos.id_infos = user_infos_adm_user.id_infos) 
  AND (user_infos_adm_user.id_user = _adm_user.id_user) 
  AND (
    (game_avatar.pseudo = 'abc@hotmail.fr') 
    OR (user_infos.pseudo = 'abc@hotmail.fr') 
    OR (_adm_user.login = 'abc@hotmail.fr') 
    OR (user_infos.id_infos = 'abc@hotmail.fr')
  )
) 
ORDER BY game_avatar.pseudo

J'ai pourtant mis des index sur l'ensemble des champs concernés.

Lorsque je fais un explain, voici le résultat (format csv) :

id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;SIMPLE;_adm_user;index;PRIMARY,login;login;767;\N;42842;Using index\; Using temporary\; Using filesort
1;SIMPLE;user_infos_adm_user;ref;id_infos,id_user;id_user;4;_adm_user.id_user;1;
1;SIMPLE;user_infos;eq_ref;PRIMARY,pseudo;PRIMARY;4;user_infos_adm_user.id_infos;1;
1;SIMPLE;game_avatar;ref;id_user,pseudo;id_user;4;user_infos.id_infos;2;Using where

Avez vous des idées de pistes me permettant d'améliorer la rapidité d'exécution de cette requête?

Merci pour toute aide!

[CinePhil]

1) La syntaxe normalisée depuis 1992 pour les jointures utilise l'opérateur JOIN.
L'utiliser t'aurait permis d'éviter cette forêt de parenthèses !

Voici ta requête récrite et remise en forme :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT user_infos.id_infos, 
    game_avatar.id_avatar, 
    game_avatar.pseudo AS avatar, 
    user_infos.pseudo AS compte, 
    _adm_user.login AS email, 
    DATE_FORMAT(FROM_UNIXTIME(game_avatar.last_connexion), '%d-%m-%Y') AS derniere_connexion, 
    IF(game_avatar.etat_avatar = 1, 'non', 'oui') AS verifie 
FROM game_avatar 
INNER JOIN user_infos ON game_avatar.id_user = user_infos.id_infos
    INNER JOIN user_infos_adm_user ON user_infos.id_infos = user_infos_adm_user.id_infos
        INNER JOIN _adm_user ON user_infos_adm_user.id_user = _adm_user.id_user
WHERE game_avatar.pseudo = 'abc@hotmail.fr'
    OR user_infos.pseudo = 'abc@hotmail.fr' 
    OR _adm_user.login = 'abc@hotmail.fr'
    OR user_infos.id_infos = 'abc@hotmail.fr'
ORDER BY game_avatar.pseudo

2) Les colonnes figurant dans les conditions de jointures (après les ON) sont-elles indexées ?

3) Les colonnes sur lesquelles portent les recherchent sont-elles indexées ?

J'ai pourtant mis des index sur l'ensemble des champs concernés.

La réponse semble être oui aux deux dernières question mais de quel type sont ces colonnes (et pas champs ) ?

4)

Code :

1
2
3
user_infos.id_infos = 'abc@hotmail.fr'
 
game_avatar.id_user = user_infos.id_infos

Comparer un id_infos qui, d'après la condition de jointure, doit être égal à un id_user, à une adrel, donc à une chaîne de caractères veut dire :
- soit que ce test est inutile car il ne sera jamais satisfait vu qu'un id ne devrait pas être de type alphanumérique ;
- soit que le choix de l'id est mauvais !

[joannesdekoster]

Merci d'avoir pris le temps de répondre à mon message!

Voici la définition de mes tables. Ca t'aidera peut être à mieux analyser la situation.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `game_avatar` (                                                        
               `id_avatar` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,                          
               `pseudo` varchar(12) collate utf8_unicode_ci NOT NULL,                            
               `date_creation` int(10) NOT NULL,                                                 
               `last_connexion` int(10) NOT NULL,                                                
               `id_user` int(10) UNSIGNED NOT NULL,                                              
               `date_validation` bigint(20) DEFAULT NULL,                                        
               `etat_avatar` tinyint(2) DEFAULT '1',                                             
               `activate` tinyint(1) NOT NULL DEFAULT '1',                                       
               PRIMARY KEY  (`id_avatar`),                                                       
               KEY `id_user` (`id_user`),                                                        
               KEY `pseudo` (`pseudo`)                                                           
             ) ENGINE=MyISAM

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE `user_infos` (                                                        
              `id_infos` int(11) NOT NULL AUTO_INCREMENT,                                      
              `date_creation` bigint(20) NOT NULL,                                             
              `id_createur` int(11) NOT NULL,                                                  
              `date_modification` bigint(20) NOT NULL,                                         
              `id_modificateur` int(11) NOT NULL,                                              
              `etat_doc` tinyint(1) NOT NULL,                                                  
              `pseudo` varchar(255) collate utf8_unicode_ci DEFAULT NULL,                      
              `etat_pseudo` int(11) NOT NULL,                                                  
              `date_validation` bigint(20) DEFAULT NULL,                                       
              `birth_date` bigint(20) DEFAULT NULL,                                            
              `key` varchar(255) collate utf8_unicode_ci NOT NULL DEFAULT '0',                 
              `password` varchar(255) collate utf8_unicode_ci DEFAULT NULL,                    
              `id_player_type` int(11) NOT NULL DEFAULT '0',                                   
              `end_subscription` bigint(20) NOT NULL DEFAULT '0',                              
              `id_tchat_type` int(11) NOT NULL DEFAULT '1',                                    
              `gender` tinyint(1) NOT NULL,                                                    
              `id_type_crisp_evaluate` int(11) NOT NULL,                                       
              `activate` tinyint(1) NOT NULL DEFAULT '2',                                      
              `key_invitation` varchar(255) character SET utf8 NOT NULL,                       
              `id_gulli` int(11) NOT NULL DEFAULT '0',                                         
              `gulli_pseudo` varchar(255) collate utf8_unicode_ci NOT NULL DEFAULT '0',        
              PRIMARY KEY  (`id_infos`),                                                       
              KEY `pseudo` (`pseudo`),                                                         
              KEY `key` (`key`)                                                                
            ) ENGINE=InnoDB

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `_adm_user` (                                                         
             `id_user` int(11) NOT NULL AUTO_INCREMENT,                                       
             `date_creation` bigint(20) NOT NULL DEFAULT '0',                                 
             `id_createur` int(11) NOT NULL DEFAULT '0',                                      
             `date_modification` bigint(20) NOT NULL DEFAULT '0',                             
             `id_modificateur` int(11) NOT NULL DEFAULT '0',                                  
             `etat_doc` tinyint(1) NOT NULL DEFAULT '0',                                      
             `key_unique` bigint(20) NOT NULL DEFAULT '0',                                    
             `login` varchar(255) collate utf8_unicode_ci NOT NULL DEFAULT '',                
             `mot_de_passe` varchar(255) collate utf8_unicode_ci NOT NULL DEFAULT '',         
             `type_connect` tinyint(4) NOT NULL DEFAULT '0',                                  
             `etat_user` tinyint(4) NOT NULL DEFAULT '0',                                     
             `login_modif` varchar(255) collate utf8_unicode_ci NOT NULL,                     
             `nwsltr_sub` tinyint(4) NOT NULL DEFAULT '1',                                    
             PRIMARY KEY  (`id_user`),                                                        
             KEY `key_unique` (`key_unique`),                                                 
             KEY `login` (`login`)                                                            
           ) ENGINE=InnoDB

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `user_infos_adm_user` (                       
                       `id_infos_id_user` int(11) NOT NULL AUTO_INCREMENT,      
                       `date_creation` bigint(20) NOT NULL,                     
                       `id_createur` int(11) NOT NULL,                          
                       `date_modification` bigint(20) NOT NULL,                 
                       `id_modificateur` int(11) NOT NULL,                      
                       `etat_doc` tinyint(4) NOT NULL,                          
                       `id_infos` int(11) NOT NULL,                             
                       `id_user` int(11) NOT NULL,                              
                       PRIMARY KEY  (`id_infos_id_user`),                       
                       KEY `id_infos` (`id_infos`),                             
                       KEY `id_user` (`id_user`)                                
                     ) ENGINE=InnoDB

Pour info, il s'agit d'une requête correspondant à un petit moteur de recherche. On souhaite chercher sur ces quelques champs, peu importe ce que saisit l'utilisateur. (il peut saisir une adresse email, un identifiant numérique ou autre donnée)

C'est grave docteur?

[joannesdekoster]

J'ai oublié les infos sur les index :

Code :

1
2
3
4
5
6
7
8
/*Index Information For - game_avatar*/
----------------------------------------------------
 
TABLE        Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  NULL    Index_type  Comment
-----------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
game_avatar           0  PRIMARY              1  id_avatar    A                87259    (NULL)  (NULL)          BTREE              
game_avatar           1  id_user              1  id_user      A                43629    (NULL)  (NULL)          BTREE              
game_avatar           1  pseudo               1  pseudo       A                87259    (NULL)  (NULL)          BTREE

Code :

1
2
3
4
5
6
7
8
/*Index Information For - user_infos*/
---------------------------------------------------
 
TABLE       Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  NULL    Index_type  Comment
----------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
user_infos           0  PRIMARY              1  id_infos     A                47740    (NULL)  (NULL)          BTREE              
user_infos           1  pseudo               1  pseudo       A                47740    (NULL)  (NULL)  YES     BTREE              
user_infos           1  KEY                  1  KEY          A                47740    (NULL)  (NULL)          BTREE

Code :

1
2
3
4
5
6
7
8
/*Index Information For - _adm_user*/
--------------------------------------------------
 
TABLE      Non_unique  Key_name    Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  NULL    Index_type  Comment
---------  ----------  ----------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------
_adm_user           0  PRIMARY                1  id_user      A                42802    (NULL)  (NULL)          BTREE              
_adm_user           1  key_unique             1  key_unique   A                42802    (NULL)  (NULL)          BTREE              
_adm_user           1  login                  1  login        A                42802    (NULL)  (NULL)          BTREE

Code :

1
2
3
4
5
6
7
8
/*Index Information For - user_infos_adm_user*/
------------------------------------------------------------
 
TABLE                Non_unique  Key_name  Seq_in_index  Column_name       Collation  Cardinality  Sub_part  Packed  NULL    Index_type  Comment
-------------------  ----------  --------  ------------  ----------------  ---------  -----------  --------  ------  ------  ----------  -------
user_infos_adm_user           0  PRIMARY              1  id_infos_id_user  A                47405    (NULL)  (NULL)          BTREE              
user_infos_adm_user           1  id_infos             1  id_infos          A                47405    (NULL)  (NULL)          BTREE              
user_infos_adm_user           1  id_user              1  id_user           A                47405    (NULL)  (NULL)          BTREE

[CinePhil]

Le seul truc qui me choque, et que j'avais déjà soupçonné par la requête à cause du FROM_UNIXTIME, c'est l'utilisation d'entiers pour des colonnes de dates.

Les tables ne sont pas énormes, la requête devrait s'exécuter en moins d'une seconde.

Quand tu dis que la requête met 5 secondes pour aboutir, c'est en la soumettant directement sur le serveur ou via un logiciel qui fait peut-être autre chose dans le même temps ?

[joannesdekoster]

Le problème des colonnes date au format entier m'avait également interpellé. J'avais déjà demandé à ce qu'on change ça mais c'est pas possible (ça ne dépend pas de moi). Je travaille pas seul sur le projet.

J'utilise sqlyog, une interface d'administration. Normalement c'est assez fiable / direct. Lorsque je lance la même requête dans phpmyadmin, ça met plus de 10 secondes!

Doit y avoir un souci sur le serveur quelque part.

Encore merci de tes réponses! Au moins je me sens pas trop seul avec mes soucis techniques

PS : j'ai testé la requête sur une copie de la base de prod et là en effet elle met quelques dizaines de millisecondes! Mystère, mystère...

[CinePhil]

Envoyé par joannesdekoster

PS : j'ai testé la requête sur une copie de la base de prod et là en effet elle met quelques dizaines de millisecondes! Mystère, mystère...

Sur le même serveur ?