optimisation requête mysql

optimisation requête mysql - SQL/NoSQL - Programmation

Marsh Posté le 01-04-2015 à 15:33:17    

Bonjour
 
Ma requête sur 20000 enregistrements prends 20s

Code :
  1. SELECT p.id_produit,
  2.                     p.nom_produit,
  3.                     p.id_modele,
  4.                     p.description_produit,
  5.                     p.ref_produit,
  6.                     p.id_cat,
  7.                     p.stock_produit,
  8.                     p.actif,
  9.                     p.new,
  10.                     p.prix_vente_produit,
  11.                     p.prix_promo,
  12.                     p.coupdecoeur,
  13.                                                            
  14.                     m.id_modele,
  15.                     m.nom_modele,
  16.                     m.id_marque,
  17.                    
  18.                     ma.nom_marque,
  19.                     ma.id_marque,
  21.                     c.nom_cat
  22.                    
  23.                     FROM tb_produits p
  25.                     LEFT JOIN tb_modeles m
  26.                     ON p.id_modele = m.id_modele
  27.                    
  28.                     LEFT JOIN tb_marques ma
  29.                     ON ma.id_marque = m.id_marque
  30.                    
  31.                     LEFT JOIN tb_cat c
  32.                     ON c.id_cat = p.id_cat


 
Je stocke les résultats dans un tableau php et ajoute une pagination mais je n'ai pas l'impression que ça vienne de la

Code :
  1. $nombreDePages  = ceil($nb_resultats / $nombreDeResultatsParPage);
  3. if (isset($_GET['page']))
  4. {
  5.     $page = intval($_GET['page']); // On récupère le numéro de la page indiqué dans l'adresse (livreor.php?page=4)
  6. }
  7. else // La variable n'existe pas, c'est la première fois qu'on charge la page
  8. {
  9.     $page = 1; // On se met sur la page 1 (par défaut)
  10. }
  12. //On calcule le numéro du premier message qu'on prend pour le LIMIT de MySQL
  13. $premierResultatAafficher = ($page - 1) * $nombreDeResultatsParPage;
  15. $query .=  ' ORDER BY id_produit ASC LIMIT ' . $premierResultatAafficher . ', ' . $nombreDeResultatsParPage;
  17. $result = mysql_query($query) or die($query . " - " . mysql_error());
  20. while($data = mysql_fetch_object($result)){
  21.     $id_produit[$i]  = $data->id_produit;
  22.     $description_produit[$i] = trim($data->description_produit);
  23.     $nom_produit[$i] = $data->nom_produit;
  24.     $nom_cat[$i] = $data->nom_cat;
  25.     $ref_produit[$i] = $data->ref_produit;
  26.     $prix_vente_produit[$i] = $data->prix_vente_produit;
  27.     $prix_promo[$i] = $data->prix_promo;
  28.     $nom_marque[$i] = $data->nom_marque;
  29.     $nom_modele[$i] = $data->nom_modele;
  30.     $stock_produit[$i] = $data->stock_produit;
  31.     $actif[$i] = $data->actif;
  32.     $new[$i] = $data->new;
  33.     $coupdecoeur[$i] = $data->coupdecoeur;
  34.    
  36.   $i++;
  37. }


 
Je pense plus tôt que c'est une question d'index mais je sèche , j'ai essayé qq trucs mais sans résultats
D'ailleurs la requête suivante est rapide  

Code :
  1. $query = 'SELECT p.id_produit,
  2.                     p.nom_produit,
  3.                     p.id_modele,
  4.                     p.description_produit,
  5.                     p.ref_produit,
  6.                     p.id_cat,
  7.                     p.stock_produit,
  8.                     p.actif,
  9.                     p.new,
  10.                     p.prix_vente_produit,
  11.                     p.prix_promo,
  12.                     p.coupdecoeur,
  13.                                                            
  14.                     m.id_modele,
  15.                     m.nom_modele,
  16.                     m.id_marque,
  17.                    
  18.                     ma.nom_marque,
  19.                     ma.id_marque,
  21.                     c.nom_cat
  22.                    
  23.                     FROM tb_produits p
  25.                     LEFT JOIN tb_modeles m
  26.                     ON p.id_modele = m.id_modele
  27.                    
  28.                     LEFT JOIN tb_marques ma
  29.                     ON ma.id_marque = m.id_marque
  30.                    
  31.                     LEFT JOIN tb_cat c
  32.                     ON c.id_cat = p.id_cat
  33.                    
  34.                    
  35.                    
  36.                     WHERE ( m.nom_modele LIKE "%'.$mot_cle.'%"
  37.                                                 OR nom_produit LIKE "%'.$mot_cle.'%"
  38.                                                 OR p.ref_produit LIKE "%'.$mot_cle.'%"
  39.                                                  )
  40.                                                
  41.                                                
  42.                     ' ;


 
Merci à ceux qui prendront le temps de me répondre :)

Reply

Marsh Posté le 01-04-2015 à 15:33:17   

Reply

Marsh Posté le 01-04-2015 à 15:46:05    

Utilise EXPLAIN dans phpMyAdmin pour voir ce qui bloque :
EXPLAIN SELECT.... FROM...
 
Après, le OR et le like, ça te tue les perfs. En général, avec LIKE, impossible d'utiliser un index. Déjà, pour virer les OR, tu peux faire ça :
'SELECT...FROM...WHERE CONCAT(m.nom_modele, nom_produit, p.ref_produit) LIKE "%'.$mot_cle.'%"';   ;)


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 01-04-2015 à 16:43:46    

Merci pour l'astuce je ne connaissais pas !
Mais cette dernière requête mets 2s justement, c'est la 1ère en haut qui met 20s !

Reply

Marsh Posté le 01-04-2015 à 16:50:59    

p.id_modele, m.id_modele, ma.id_marque, m.id_marque, c.id_cat, p.id_cat sont tous déclarés clé primaire ou clé étrangère (suivant le cas) ?
 
Ca donne quoi explain ?
 
Parce que je suis étonné des perfs : mon appli Astres a une requête sur les tickets qui fait des LEFT JOIN sur bien 6-7 tables dont une fait plus de 30000 enregistrements et ça met pas 20s. Ton appli tourne sur quel matos ? Moi, c'est un CPU 2.5 Ghz double coeur, 2 Go de ram.
 
Tu peux aussi tuner le fichier de conf de Mysql pour augmenter la taille de certains caches, buffers, nb de tables temporaires ouvertes en même temps ou taille des tables temporaires en mémoire ou sur le hdd...
 


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 01-04-2015 à 23:37:39    

toutes déclarées en primaire dans leur tables respectives, pas de clé étrangères car MySam
 
 
Voilà le explain
 
EXPLAIN  SELECT p.id_produit, p.nom_produit, p.id_modele, p.description_produit, p.ref_produit, p.id_cat, p.stock_produit, p.actif, p.new, p.prix_vente_produit, p.prix_promo, p.coupdecoeur, m.id_modele, m.nom_modele, m.id_marque, ma.nom_marque, ma.id_marque, c.nom_cat
FROM tb_produits p
LEFT  JOIN tb_modeles m ON p.id_modele = m.id_modele
LEFT  JOIN tb_marques ma ON ma.id_marque = m.id_marque
LEFT  JOIN tb_cat c ON c.id_cat = p.id_cat
ORDER  BY id_produit
 
 
id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     p     ALL     NULL     NULL     NULL     NULL     20321     Using filesort
1     SIMPLE     m     eq_ref     PRIMARY     PRIMARY     4     transforbike.p.id_modele     1      
1     SIMPLE     ma     eq_ref     PRIMARY     PRIMARY     4     transforbike.m.id_marque     1      
1     SIMPLE     c     eq_ref     PRIMARY     PRIMARY     4     transforbike.p.id_cat     1
 
 
J'ai juste fait un coup de mysqltuner pour le my.conf
3Go de mémoire, CPU 2Ghz
 
Sur quoi dois-je faire les index ?


Message édité par networkinfo le 01-04-2015 à 23:49:43
Reply

Marsh Posté le 02-04-2015 à 00:06:21    

c'est résolu
 
Le pb vient de mon php car je calcule le nombre total de réponse sur la même requête, je la fais donc 2 fois
 
J'ai aussi ajouté un mysql_free_result avant de faire la vrai requête
merci !

Reply

Marsh Posté le 02-04-2015 à 10:17:28    

Pour les clés étrangères, mets un index sur ces champs. ;)


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 02-04-2015 à 14:05:41    

Merci :)

 

Et faire ça

Code :
  1. $result = mysql_query($query) or die($query . " - " . mysql_error());
  2. while($data = mysql_fetch_object($result)){
  3.     $id_produit[$i]  = $data->id_produit;
  4.     $description_produit[$i] = trim($data->description_produit);
  5.     $nom_produit[$i] = $data->nom_produit;
  6.     $nom_cat[$i] = $data->nom_cat;
  7.     $ref_produit[$i] = $data->ref_produit;
  8.     $prix_vente_produit[$i] = $data->prix_vente_produit;
  9.     $prix_promo[$i] = $data->prix_promo;
  10.     $nom_marque[$i] = $data->nom_marque;
  11.     $nom_modele[$i] = $data->nom_modele;
  12.     $stock_produit[$i] = $data->stock_produit;
  13.     $actif[$i] = $data->actif;
  14.     $new[$i] = $data->new;
  15.     $coupdecoeur[$i] = $data->coupdecoeur;
  16.  
  17.   $i++;
  18. }


 Puis ça

 
Code :
  1. <?php for($i=0;$i<count($id_produit);$i++){ ?>
  2. <?php echo $id_produit[$i]; ?></td>
  3. <?php } ?>
 

c'est mauvais ?


Message édité par networkinfo le 02-04-2015 à 14:08:19
Reply

Marsh Posté le 02-04-2015 à 14:27:34    

Dans l'absolut, non ce n'est pas mauvais mais si tu ne fait aucune action en php sur tes tableaux (changement de tri, filtre ou autre), tu peux utiliser directement ton retour sql pour l'affichage...
 
Le plus couteux (hormis le poids de tes différents tableaux en mémoire) c'est sans doute ton :

Code :
  1. for($i=0;$i<count($id_produit);$i++){


Soit tu fait

Code :
  1. $nb_prod = count($id_produit);
  2. for($i=0;$i<$nb_prod;$i++){


Ou alors

Code :
  1. foreach($id_produit as $i => $value){


 
Personnellement j'ai tendance à faire :

Code :
  1. $t_prod = array();
  2. while($data = mysql_fetch_assoc($result)){
  3.     $t_prod[$data['id_produit']] = $data;
  4. }
  5. print_r($t_prod);


Message édité par mechkurt le 02-04-2015 à 14:28:00

---------------
D3
Reply

Marsh Posté le 02-04-2015 à 14:58:49    

Pareil, pour la récup de données provenant d'une BD, je fais un while. Ca évite de passer par un tableau intermédiaire qui peut être consommateur de mémoire.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 02-04-2015 à 14:58:49   

Reply

Marsh Posté le 02-04-2015 à 15:02:10    

Merci les gars c'est sympa
 
Et pour la pagination ?
Vous faîtes un mysql_num_row sur une requête simplifiée et vous ajoutez le limit en conséquence ?

Reply

Marsh Posté le 02-04-2015 à 15:04:32    

Moi, je fais un COUNT() sur une requête simplifiée puis la vrai requête pour avoir les enregistrements à afficher.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed