Requête MySql imbriquée

poupilou · 8 Octobre 2020

Salut à tous,

J'ai cette requête MySql ci-dessous qui fonctionne mais qui est très lente, je voudrais l'optimiser, je me demandais donc si c'était possible de passer par une requête imbriquée pour que ce soit plus rapide et d'éviter la double boucle while qui ralentit fortement l'affichage des résultats.

Cette requête sert à afficher des données dans un graphique.

Ma table MySql contient plus de 650 000 lignes.

Code:

$data_X_1 = "";
$data_Y_1 = "";
$requete1 = get_requete("SELECT DISTINCT YEAR(`date`) FROM `ma_table` WHERE critere1='$critere1' AND critere2='$critere2' AND critere3='$critere3' AND critere4!=0 ORDER BY `date`");
while($voir1 = $MysqliFetchArray($requete1))
{   
$requete = get_requete("SELECT critere4 FROM `ma_table` WHERE YEAR(`date`)='".$voir1[0]."' AND critere1='$critere1' AND critere2='$critere2' AND critere3='$critere3' ORDER BY `date` DESC LIMIT 1");
while($voir = $MysqliFetchArray($requete))
{
$data_X_1 .= "'".$voir1[0]."', ";
$data_Y_1 .= "$voir['critere4']";
}
}

Quelqu'un voit-il une solution pour optimiser ma requête ?

D'avance merci pour votre aide.

ABCWEB · 8 Octobre 2020

je connais pas ta structure de table mais je pense qu'un left join pourrait faire le boulot. Il y a plein de tuto sur les jointures sql je pense qu'il y a forcement une solution à ton problème

poupilou · 8 Octobre 2020

Merci pour ta réponse.

A priori on peut effectivement faire un LEFT OUTER JOIN sur la même table mais je ne vois pas comment faire avec le DISTINCT YEAR(`date`) et le ORDER BY `date` de ma première requête, les 2 requêtes ci-dessous ne fonctionnent pas :

Code:

SELECT DISTINCT YEAR(`t1`.`date`) FROM `ma_table` as `t1`
WHERE critere1='$critere1' AND critere2='$critere2' AND critere3='$critere3' AND critere4!=0 ORDER BY `t1`.`date`
LEFT OUTER JOIN `ma_table` as `t2` ON YEAR(`t2`.`date`)=YEAR(`t1`.`date`) AND `t2`.critere1='$critere1' AND `t2`.critere2='$critere2' AND `t2`.critere3='$critere3'
ORDER BY `t2`.`date` DESC LIMIT 1

Ou :

Code:

SELECT DISTINCT YEAR(`t1`.`date`) FROM `ma_table` as `t1`
LEFT OUTER JOIN `ma_table` as `t2` ON YEAR(`t2`.`date`)=YEAR(`t1`.`date`) AND `t2`.critere1='$critere1' AND `t2`.critere2='$critere2' AND `t2`.critere3='$critere3'
ORDER BY `t2`.`date` DESC LIMIT 1

emualliug · 8 Octobre 2020

Sans la structure et sans savoir ce que tu veux faire concrètement, ce n'est pas aisé.

Mais si je comprends bien, tu cherches à récupérer la valeur `critere4` de la dernière date de chaque année (sauf celles où aucune valeur `critere4` n'est différente de 0) depuis `ma_table` ou `critere1` = X, `critere2` = Y et `critere3` = Z, le tout classé par année croissante ?

poupilou · 8 Octobre 2020

emualliug a dit:
Sans la structure et sans savoir ce que tu veux faire concrètement, ce n'est pas aisé.

Mais si je comprends bien, tu cherches à récupérer la valeur `critere4` de la dernière date de chaque année (sauf celles où aucune valeur `critere4` n'est différente de 0) depuis `ma_table` ou `critere1` = X, `critere2` = Y et `critere3` = Z, le tout classé par année croissante ?

Oui c'est bien cela.

spout · 8 Octobre 2020

Tu px créer un schéma sur SQL Fiddle pour partager ta structure avec les WRInautes: http://sqlfiddle.com/

poupilou · 9 Octobre 2020

Merci Spout.

Voici un modèle de schéma de ma table mysql : http://sqlfiddle.com/#!9/7ce978/3

Désolé mais je ne sais pas comment on fait une boucle while avec SQL Fiddle.

On cherche à afficher le nombre totale des vente annuelles des 3008 de couleur blanc pour chaque année (2020 compris, même si cette année n'est pas encore terminée).

On doit donc obtenir comme résultats :

2018 => 35
2019 => 65
2020 => 25

spout · 9 Octobre 2020

Pour 2020, ta condition `date` LIKE '%-12-01%' filtre tout.

poupilou · 9 Octobre 2020

Oui je sais mais comment faire pour 2020 puisque cette année n'est pas terminée ? Pour 2020 il faut afficher les ventes du dernier mois présent dans la table, soit le mois de septembre (2020-09-01), comment faire cela dans la même requête ou dans une requête imbriquée ?

emualliug · 9 Octobre 2020

OK, c'est bien plus clair.
On peut faire beaucoup plus simple :

Code:

SELECT YEAR(`date`) AS `millesime` ,
SUM(`nbre_vente_mensuelle`) AS `total_vente_annuelle`
FROM `vente_voiture`
WHERE `modele` LIKE '3008' AND `couleur` LIKE 'blanc' AND `nbre_vente_mensuelle`!=0
GROUP BY `millesime`;

Quelques observations :
À noter que je n'utilise pas la colonne `nbre_vente_annuelle`, à dessein. On pourrait simplifier la requête en l'utilisant, mais je trouve cette colonne moche dans ton format de base de donnée.
Pareil, `date` est une date, autant l'utiliser comme tel, pas comme une chaîne de caractère.
Enfin, si tu as un tableau des ventes (modele, date de vente) on pourrait entièrement se passer de cette table qui cumul les ventes. Schéma qui serait beaucoup plus propre.

poupilou · 9 Octobre 2020

emualliug a dit:
OK, c'est bien plus clair.
On peut faire beaucoup plus simple :

Code:

SELECT YEAR(`date`) AS `millesime` , SUM(`nbre_vente_mensuelle`) AS `total_vente_annuelle` FROM `vente_voiture` WHERE `modele` LIKE '3008' AND `couleur` LIKE 'blanc' AND `nbre_vente_mensuelle`!=0 GROUP BY `millesime`;

Quelques observations :
À noter que je n'utilise pas la colonne `nbre_vente_annuelle`, à dessein. On pourrait simplifier la requête en l'utilisant, mais je trouve cette colonne moche dans ton format de base de donnée.
Pareil, `date` est une date, autant l'utiliser comme tel, pas comme une chaîne de caractère.
Enfin, si tu as un tableau des ventes (modele, date de vente) on pourrait entièrement se passer de cette table qui cumul les ventes. Schéma qui serait beaucoup plus propre.

Merci pour ton code qui fonctionne parfaitement bien.

Tu dis :

emualliug a dit:
À noter que je n'utilise pas la colonne `nbre_vente_annuelle`, à dessein. On pourrait simplifier la requête en l'utilisant

Quelle serait cette requête en utilisant la colonne `nbre_vente_annuelle` ?

Je rappelle que ma table MySql contient plus de 650 000 lignes.

emualliug · 9 Octobre 2020

Je pensais à un truc à base de sous-requête pour éviter SUM(), mais à l'essai j'ai l'impression que c'est moins efficient encore.

Des centaines de milliers de ligne ne me paraîssent pas excessif pour une base SQL. Si c'est lent, il faut essayer d'améliorer le serveur de la BDD ou repenser au schéma de la base de donnée.

poupilou · 9 Octobre 2020

emualliug a dit:
Des centaines de milliers de ligne ne me paraîssent pas excessif pour une base SQL.

Ce n'est pas 650 000 lignes pour toute ma base MySql (qui comporte plusieurs tables) mais 650 000 lignes pour une seule table MySql (qui a 11 colonnes) et qui va grossir de plusieurs milliers de lignes chaque mois.

emualliug · 9 Octobre 2020

Oui, c'est comme ça que je l'avais compris.

Les SGBD sont quand même effroyablement puissants, quoique cela varie un peu suivant les logiciels. Mais je suis souvent impressionné de la facilité qu'ils ont de manipuler rapidement un nombre important de données. C'est rendu possible quand les données sont bien structurées, et quand on utilise au mieux les fonctions implémentées au cœur du SGBD ; c'est pour ça que pour un DATE, je préfère utiliser des fonctions de manipulation des dates que comparer des chaînes de caractère. Les jointures, pour bordéliques qu'elles puissent paraître sont souvent fort bien gérées.

Le nombre de colonnes ne m'impressionne pas plus que ça, car on évite de les consulter toutes, la comparaison et le tri ne portent que sur certaines d'entre elles. Mais si on veut supprimer `nbre_vente_annuelle` ce sera toujours ça de gagné ! Mais honnêtement ce n'est pas le soucis, une colonne non manipulée va grossir (un tout petit peu) la taille de la BDD, mais ne va pas influencer sur les performances des requêtes. C'est surtout que je n'aime pas les calculs intermédiaires. Par exemple, la voiture 3008 blanche vendue le 3 mars 2019, n'était pas blanche, mais "perle" ; il faudrait modifier les chiffres du mois de mars 2019 (passe encore), mais du coup le total annuel placé dans mars 2019, avril 2019, mai 2019, juin 2019, etc.

Si vraiment il fallait avoir le montant annuel précalculé je m'abstiendrai absolument de le faire comme tu l'as fait, parce que pour moi ça n'a pas de sens (la donnée stockée dans `nbre_vente_annuelle` dans la ligne mars 2019 n'a de sens que si elle consultée en avril 2019, au delà elle n'en a plus, et pourtant on la conserve). Deux solutions, soit avoir deux tables, une `vente_voiture_mensuelle` et une `vente_voiture_annuelle` soit changer les colonnes dans `vente_voiture` en ajoutant une colonne `type_total` avec une valeur de type ENUM ('mensuel', 'annuel') et `total_periode` qui remplacent `nbre_vente_mensuelle` et `nbre_vente_annuelle`).

Je serais surtout tenté de me passer totalement de cette table `vente_voiture` si une autre table reprend toutes les transactions. Je me demande si compter le nombre de transactions ne serait pas in fine plus rapide, j'ai l'impression qu'un COUNT est assez efficace, plus qu'un SUM, même si le nombre de ligne est bien plus important.

emualliug · 9 Octobre 2020

poupilou a dit:
Quelle serait cette requête en utilisant la colonne `nbre_vente_annuelle` ?

Parfois les solutions les plus simples ne m'arrivent pas de suite… pas besoin de sous-requête, un MAX() suffit :

Code:

SELECT YEAR(`date`) AS `millesime` ,
MAX(`nbre_vente_annuelle`) AS `total_vente_annuelle`
FROM `vente_voiture`
WHERE `modele` LIKE '3008' AND `couleur` LIKE 'blanc' AND `nbre_vente_mensuelle`!=0
GROUP BY `millesime`;

Mais ça ne m'a pas l'air plus rapide qu'avec un SUM(). Ce qui n'est pas forcément étonnant d'ailleurs.

poupilou · 10 Octobre 2020

emualliug a dit:
Parfois les solutions les plus simples ne m'arrivent pas de suite… pas besoin de sous-requête, un MAX() suffit :

Code:

SELECT YEAR(`date`) AS `millesime` , MAX(`nbre_vente_annuelle`) AS `total_vente_annuelle` FROM `vente_voiture` WHERE `modele` LIKE '3008' AND `couleur` LIKE 'blanc' AND `nbre_vente_mensuelle`!=0 GROUP BY `millesime`;

Mais ça ne m'a pas l'air plus rapide qu'avec un SUM(). Ce qui n'est pas forcément étonnant d'ailleurs.

Cette requête fonctionne bien mais effectivement elle n'est pas plus rapide qu'avec le SUM().

J'ai ajouté des colonnes "total_vente_mesuelle" et "total_vente_annuelle" pour éviter de faire des calculs lors de mes requêtes mysql car les données (calculées) sont déjà stockées dans la colonne "total_vente_annuelle". Je pensais que c'était plus rapide de trier des données (via la colonne "total_vente_annuelle") que de calculer ces données à partir de la colonne "total_vente_mensuelle" et de les trier par ordre croissant ou décroissant.

J'ai par exemple un "top 10 des meilleures ventes totales" : quelles sont les voitures qui se sont le plus vendues.

Un "top 10 des meilleures ventes annuelles (2020)" : quelles sont les voitures qui se sont le plus vendues cette année (2020).

Un "top 10 des meilleures ventes mensuelles" : quelles sont les voitures qui se sont le plus vendues ce mois.

Faut-il juste avoir une colonne "total_vente_mensuelle" dans ma table et faire le calcul à chaque fois pour établir ces top 10 ou bien avoir des colonnes "total_vente_mensuelle" , "total_vente_annuelle" et "total_vente" dans lesquelles on a déjà calculé les données et qu'on peut trier (ces colonnes) par un ORDER BY ?

Dr DLP · 10 Octobre 2020

Rajouter une colonne pour y stocker des résultats de requête ne fera qu'alourdir la table.
Pourquoi ne pas faire le calcul via une tache cron fictive, disons toutes les heures, et générer un fichier cache qui les contient?

Je te mets le code que j'utilise à titre d'exemple:

Code:

    // 10 derniers commentaires
    $sql = "SELECT c.*, u.*
            FROM lm_p_comments AS c
            LEFT JOIN lm_p_users AS u
            ON c.u_id = u.id
            WHERE c.is_allowed = 1
            AND c.c_tpl LIKE '%us%'
            ORDER BY RAND() LIMIT 10";
    $result = mysqli_query($db, $sql);
    $c = mysqli_num_rows($result);
    $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
    $co = array();
    for ($i = 0; $i < $c; $i++)
    {
        // Décomposition du tpl
        // Quelque soit la langue, le tpl fait toujours 12 caractères avant la catégorie
        $co[$i][0] = substr($row[$i]['c_tpl'], 15, 1);
        $co[$i][1] = substr($row[$i]['c_tpl'], 17, 1);   
        $co[$i][2] = $row[$i]['rating'];

        // Recherche d'une image correspondante au template
        $dir_to_scan = "./templates/img/US/" . $co[$i][0] . "/" . $co[$i][1] . "/";
        $images = glob($dir_to_scan."*.{jpg,gif,png,bmp}",GLOB_BRACE);
        $co[$i][3] = $images[rand(0, count($images)-1)];
        
        // Données utilisateurs
        $co[$i][4] = $row[$i]['u_pseudo'];       
        if(isset($row[$i]['u_title']) || isset($row[$i]['u_spec']))
        {
            if(isset($row[$i]['u_title']) && isset($row[$i]['u_spec']))
            {
                $co[$i][5] = $row[$i]['u_title'] . " (" . $row[$i]['u_spec'] . ")";
            }
            else
            {
                $co[$i][5] = $row[$i]['u_title'] . $row[$i]['u_spec'];
            }
        }
        $co[$i][5] = $co[$i][5];       
        $co[$i][6] = $row[$i]['comment'];
    }   
    
    // Génération du fichier cache
   $page = '<?php' . "\n\n";
   $page .= 'if(!defined(\'in_prog\')) { exit; }' . "\n\n";
   $page .= '$num_tpl = ' . $num_tpl . ';' . "\n\n";   
   $page .= '$num_visites = ' . $num_visites . ';' . "\n\n";   
   $page .= '$num_users = ' . $num_users . ';' . "\n\n";   
   $page .= '$cs = \'' . serialize($cs) . '\';' . "\n\n";
   $page .= '$co = \'' . serialize($co) . '\';' . "\n\n";   
   $page .= '?>';
   @chmod($file,0755);
   $fw = fopen($file, "w");
   fputs($fw,$page, strlen(trim($page)));
   fclose($fw);
  
   // Mise à jour de l'heure de mise à jour
   $sql = "UPDATE lm_p_config SET lm_p_value = '" . time() . "' WHERE lm_p_config = 'last_index'";
   mysqli_query($db, $sql);

A dispo pour l'adapter

colonies · 10 Octobre 2020

Dr DLP a dit:
Code:

// Génération du fichier cache $page = '<?php' . "\n\n";

Il ne faut jamais écrire un cache en tant que script PHP, ou alors ne pas passer par fputs mais par une fonction.

La raison, c'est qu'il est possible de désactiver la vérification du filemtime des fichiers PHP par le cache d'OPCode pour améliorer les performances.
Le jour où tu actives cette option... les caches sont lus/compilés une fois et restent en mémoire, même si le fichier change. Donc les caches restent figés à leur première lecture/compilation.
Tu peux t'amuser à retrouver tous ces caches et ajouter des opcache_invalidate partout, ou bien tu as fait des json et il n'y a pas ce problème... ou bien tu as une fonction spéciale pour l'écriture des fichiers cache, et tu n'as qu'un opcache_invalidate a ajouter dans cette fonction pour éviter les problèmes.

emualliug · 12 Octobre 2020

poupilou a dit:
Faut-il juste avoir une colonne "total_vente_mensuelle" dans ma table et faire le calcul à chaque fois pour établir ces top 10 ou bien avoir des colonnes "total_vente_mensuelle" , "total_vente_annuelle" et "total_vente" dans lesquelles on a déjà calculé les données et qu'on peut trier (ces colonnes) par un ORDER BY ?

Je comprends un peu mieux l'idée de faire des calculs intermédiaires pour faire des "top". Dans une certaine mesure, ça pourrait peut-être accélérer le calcul, et encore, ce serait à vérifier (en tout cas pas dans sa forme actuelle). En tout état de cause, ça me paraît un intérêt secondaire.

Tel quel, faire un "top" sur ta base en comptant sur les totaux précalculés ne me semble pas du tout idéal, parce que en cherchant le total annuel, tu vas voir réapparaître les totaux annuels précalculés de chaque mois. Et puis ça t'oblige à n'utiliser que les totaux pour les couples (modele, couleur), or tu voudrais peut-être aussi faire un top 10 sur les modèles les plus vendus, les couleurs les plus populaires, ou les couleurs les plus vendues pour une 3008.

Pour revenir à ton hypothèse de départ, (modele, couleur) les plus vendus en 2018 :

Code:

SELECT `modele`, `couleur`,
SUM(`nbre_vente_mensuelle`) AS `total_vente_annuelle`
FROM `vente_voiture`
WHERE YEAR(`date`)=2018
GROUP BY `modele`, `couleur`
ORDER BY `total_vente_annuelle` DESC;

Revenons aux performances. Si les performances sont mises à l'épreuve parce que les pages sont très consultées, et qu'il s'agit à chaque fois de le même chose (mais ça nécessite pourtant à chaque fois de refaire toutes les requêtes) et que du coup le serveur SQL est sur les rotules : met les pages en cache ; toute la page (ou du moins le 'cœur' de la page), avec un rafraîchi toutes les 15 minutes (ou autre, à adapter). Là tu gagneras en performance, et de manière très sensible, puisqu'il n'y aura pratiquement plus d'appel à la BDD.

poupilou · 13 Octobre 2020

Merci pour vos réponses.

La mise en cache de ma page avec les Top 10 me semble une bonne solution en terme de performance puisque je n'ajoute des lignes dans ma table MySql qu'une fois par mois.

Je n'ai encore jamais utilisé cette technique de mise en cache des pages sur mon site : par ou commencer pour déployer cette technique ?

spout · 13 Octobre 2020

https://blog.nicolashachet.com/developpement-php/optimisation-web-php-des-caches-a-tous-les-niveaux/

colonies · 13 Octobre 2020

spout a dit:
https://blog.nicolashachet.com/developpement-php/optimisation-web-php-des-caches-a-tous-les-niveaux/

7-8 ans, c’est long. La partie sur le cache PHP... ah ah ah ! Je ne suis pas allé plus loin, mais un truc un peu plus à jour serait certainement préférable.

spout · 14 Octobre 2020

@colonies c'était surtout pour souligner qu'il y a plusieurs types de cache.
Pour le cache applicatif j'utilise Redis (avec Laravel ou Django).
Et c'est aussi simple que ça à utiliser :

PHP:

$value = Cache::remember('users', $seconds, function () {
    return DB::table('users')->get();
});

poupilou · 14 Octobre 2020

Pour infos :

- J'utilise sur mes sites les en-têtes HTTP Cache-Control.

- J'utilise php 7.2 et l'extension Zend est activé d'office sur cette version de php, avec l'option php_opcache activé (un système de cache de l'OPCode de PHP).

- La page qui affichera les Top 10 est accessible uniquement par abonnement et les url comporteront donc un ID unique de connexion pour chaque abonné (du style : www.monsite.com/abonnement/index.php?rub=top10&id=564dfgt2566gopipx78), est-ce dans ce cas de figure il est possible d'utiliser pour ces urls uniques un cache applicatif comme le suggère spout ?

emualliug · 14 Octobre 2020

poupilou a dit:
les url comporteront donc un ID unique de connexion pour chaque abonné

Clairement pas une bonne idée.

spout · 14 Octobre 2020

Si les données en cache sont identiques, l'identifiant du cache devrait l'être aussi.