Requête MySql imbriquée

Discussion dans 'Développement d'un site Web ou d'une appli mobile' créé par poupilou, 8 Octobre 2020.

  1. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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.
     
  2. ABCWEB
    ABCWEB WRInaute impliqué
    Inscrit:
    22 Octobre 2015
    Messages:
    967
    J'aime reçus:
    113
    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
     
  3. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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
    
     
  4. emualliug
    emualliug WRInaute discret
    Inscrit:
    1 Février 2020
    Messages:
    238
    J'aime reçus:
    44
    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 ?
     
  5. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    Oui c'est bien cela.
     
  6. spout
    spout WRInaute accro
    Inscrit:
    14 Mai 2003
    Messages:
    9 101
    J'aime reçus:
    304
    Tu px créer un schéma sur SQL Fiddle pour partager ta structure avec les WRInautes: http://sqlfiddle.com/
     
    #6 spout, 8 Octobre 2020
    Dernière édition: 8 Octobre 2020
  7. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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
     
  8. spout
    spout WRInaute accro
    Inscrit:
    14 Mai 2003
    Messages:
    9 101
    J'aime reçus:
    304
    Pour 2020, ta condition `date` LIKE '%-12-01%' filtre tout.
     
  9. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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 ?
     
  10. emualliug
    emualliug WRInaute discret
    Inscrit:
    1 Février 2020
    Messages:
    238
    J'aime reçus:
    44
    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.
     
    spout apprécie ceci.
  11. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    Merci pour ton code qui fonctionne parfaitement bien.

    Tu dis :

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

    Je rappelle que ma table MySql contient plus de 650 000 lignes.
     
  12. emualliug
    emualliug WRInaute discret
    Inscrit:
    1 Février 2020
    Messages:
    238
    J'aime reçus:
    44
    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.
     
  13. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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.
     
  14. emualliug
    emualliug WRInaute discret
    Inscrit:
    1 Février 2020
    Messages:
    238
    J'aime reçus:
    44
    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.
     
    spout apprécie ceci.
  15. emualliug
    emualliug WRInaute discret
    Inscrit:
    1 Février 2020
    Messages:
    238
    J'aime reçus:
    44
    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.
     
  16. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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 ?
     
  17. Dr DLP
    Dr DLP WRInaute impliqué
    Inscrit:
    29 Juin 2003
    Messages:
    530
    J'aime reçus:
    0
    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 :)
     
  18. colonies
    colonies WRInaute impliqué
    Inscrit:
    10 Septembre 2006
    Messages:
    564
    J'aime reçus:
    70
    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.
     
    #18 colonies, 10 Octobre 2020
    Dernière édition: 10 Octobre 2020
  19. emualliug
    emualliug WRInaute discret
    Inscrit:
    1 Février 2020
    Messages:
    238
    J'aime reçus:
    44
    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.
     
  20. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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 ?
     
  21. colonies
    colonies WRInaute impliqué
    Inscrit:
    10 Septembre 2006
    Messages:
    564
    J'aime reçus:
    70
    spout apprécie ceci.
  22. spout
    spout WRInaute accro
    Inscrit:
    14 Mai 2003
    Messages:
    9 101
    J'aime reçus:
    304
    @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();
    });
     
  23. poupilou
    poupilou WRInaute impliqué
    Inscrit:
    9 Février 2004
    Messages:
    838
    J'aime reçus:
    10
    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 ?
     
  24. emualliug
    emualliug WRInaute discret
    Inscrit:
    1 Février 2020
    Messages:
    238
    J'aime reçus:
    44
    Clairement pas une bonne idée.
     
  25. spout
    spout WRInaute accro
    Inscrit:
    14 Mai 2003
    Messages:
    9 101
    J'aime reçus:
    304
    Si les données en cache sont identiques, l'identifiant du cache devrait l'être aussi.
     
Chargement...
Similar Threads - Requête MySql imbriquée Forum Date
Mysql requête imbriquée Développement d'un site Web ou d'une appli mobile 1 Octobre 2015
encodage texte sur requete mysql Demandes d'avis et de conseils sur vos sites 21 Octobre 2020
Requête Mysql avec des string Développement d'un site Web ou d'une appli mobile 6 Février 2018
Requete mysql Développement d'un site Web ou d'une appli mobile 30 Mai 2017
Problème requête mysql Développement d'un site Web ou d'une appli mobile 1 Mars 2017
[php/mysql] Eviter de faire 20 requêtes pour un affichage Développement d'un site Web ou d'une appli mobile 19 Janvier 2016
requete Mysql et classement des résultats Administration d'un site Web 1 Novembre 2015
[MySQL] Requête SELECT et INSERT entre 3 tables liées+Aide Développement d'un site Web ou d'une appli mobile 30 Avril 2014
Question pour une requête MYSQL Développement d'un site Web ou d'une appli mobile 29 Mai 2013
Réunir 2 requêtes MySql Développement d'un site Web ou d'une appli mobile 22 Mai 2013