optimisation requête mysql

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : optimisation requête mysql

networkinfo

Bonjour

Ma requête sur 20000 enregistrements prends 20s

Code :

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

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 :

$nombreDePages = ceil($nb_resultats / $nombreDeResultatsParPage);
if (isset($_GET['page']))
{
$page = intval($_GET['page']); // On récupère le numéro de la page indiqué dans l'adresse (livreor.php?page=4)
}
else // La variable n'existe pas, c'est la première fois qu'on charge la page
{
$page = 1; // On se met sur la page 1 (par défaut)
}
//On calcule le numéro du premier message qu'on prend pour le LIMIT de MySQL
$premierResultatAafficher = ($page - 1) * $nombreDeResultatsParPage;
$query .= ' ORDER BY id_produit ASC LIMIT ' . $premierResultatAafficher . ', ' . $nombreDeResultatsParPage;
$result = mysql_query($query) or die($query . " - " . mysql_error());
while($data = mysql_fetch_object($result)){
$id_produit[$i] = $data->id_produit;
$description_produit[$i] = trim($data->description_produit);
$nom_produit[$i] = $data->nom_produit;
$nom_cat[$i] = $data->nom_cat;
$ref_produit[$i] = $data->ref_produit;
$prix_vente_produit[$i] = $data->prix_vente_produit;
$prix_promo[$i] = $data->prix_promo;
$nom_marque[$i] = $data->nom_marque;
$nom_modele[$i] = $data->nom_modele;
$stock_produit[$i] = $data->stock_produit;
$actif[$i] = $data->actif;
$new[$i] = $data->new;
$coupdecoeur[$i] = $data->coupdecoeur;
$i++;
}

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 :

$query = '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
WHERE ( m.nom_modele LIKE "%'.$mot_cle.'%"
OR nom_produit LIKE "%'.$mot_cle.'%"
OR p.ref_produit LIKE "%'.$mot_cle.'%"
)
' ;

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

Publicité

rufo

Pas me confondre avec Lycos!

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

networkinfo

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 !

rufo

Pas me confondre avec Lycos!

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...

networkinfo

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

networkinfo

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 !

rufo

Pas me confondre avec Lycos!

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

networkinfo

Merci

Et faire ça

Code :

$result = mysql_query($query) or die($query . " - " . mysql_error());
while($data = mysql_fetch_object($result)){
$id_produit[$i] = $data->id_produit;
$description_produit[$i] = trim($data->description_produit);
$nom_produit[$i] = $data->nom_produit;
$nom_cat[$i] = $data->nom_cat;
$ref_produit[$i] = $data->ref_produit;
$prix_vente_produit[$i] = $data->prix_vente_produit;
$prix_promo[$i] = $data->prix_promo;
$nom_marque[$i] = $data->nom_marque;
$nom_modele[$i] = $data->nom_modele;
$stock_produit[$i] = $data->stock_produit;
$actif[$i] = $data->actif;
$new[$i] = $data->new;
$coupdecoeur[$i] = $data->coupdecoeur;
$i++;
}

Puis ça

Code :

<?php for($i=0;$i<count($id_produit);$i++){ ?>
<?php echo $id_produit[$i]; ?></td>
<?php } ?>

c'est mauvais ?

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

mechkurt

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 :

for($i=0;$i<count($id_produit);$i++){

Soit tu fait

Code :

$nb_prod = count($id_produit);
for($i=0;$i<$nb_prod;$i++){

Ou alors

Code :

foreach($id_produit as $i => $value){

Personnellement j'ai tendance à faire :

Code :

$t_prod = array();
while($data = mysql_fetch_assoc($result)){
$t_prod[$data['id_produit']] = $data;
}
print_r($t_prod);

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

---------------
D3

rufo

Pas me confondre avec Lycos!

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.

Publicité

networkinfo

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 ?

rufo

Pas me confondre avec Lycos!

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

FORUM HardWare.fr

Programmation

SQL/NoSQL

optimisation requête mysql

Sujets relatifs
Optimisation d'une requête MySQL	[MySQL] Optimisation de requete
Optimisation requête (Mysql)	MySQL - Optimisation d'une requête avec plusieurs JOIN
Optimisation d'une requete mysql	[MySQL] Besoin d'aide - Optimisation d'une requête très lourde
[MySQL] Optimisation requete sur ENORME table ...	[PHP - MYSQL] optimisation d'une requete
[mysql]optimisation d'un requete	[MySQL]optimisation requete
Plus de sujets relatifs à : optimisation requête mysql

Page générée en 0.111 secondes