Optimaise Query SQL

By kyle1009

I have a query to calculate how many students of a university (divided by genre) eat food categories (for example vegetables, meat, fish….).

Each type of dish in my database has an id dish_composition. I write the query but for calculate all categories uses 711 seconds (12 minutes). It’s very inefficient. Can you help me to reduce time of execution?

This is the query:

<?php
$query=(SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition BETWEEN 51 AND 54 GROUP BY dish.dish_composition) carne)
    UNION ALL
    (SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition BETWEEN 10 AND 34 GROUP BY dish.dish_composition) primo)
    UNION ALL
    (SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition BETWEEN 60 AND 62 GROUP BY dish.dish_composition) pesce)
    UNION ALL
    (SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition IN (71,72,81,82,93) GROUP BY dish.dish_composition) verdura)
    UNION ALL
    (SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition BETWEEN 91 AND 92 GROUP BY dish.dish_composition) uova)
    UNION ALL
    (SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition IN (101,101,212) GROUP BY dish.dish_composition) formaggi)
    UNION ALL
    (SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition=83 GROUP BY dish.dish_composition) legumi)
    UNION ALL
    (SELECT SUM(uomini) as uomini_carne, SUM(donne) as donne_carne FROM ( SELECT SUM(case when person.sex='M' then 1 end) as uomini, SUM(case when person.sex='F' then 1 end) as donne FROM `dish` JOIN dish_bought ON dish.id_dish=dish_bought.id_dish JOIN meal_receipt ON dish_bought.id_receipt=meal_receipt.id_receipt JOIN relation ON meal_receipt.id_stu=relation.id_stu JOIN person ON relation.id_pers=person.id_pers WHERE dish.dish_composition=415 GROUP BY dish.dish_composition) frutta)";
?>

Source: Stack Overflow

    

Share it with your friends!

    Fatal error: Uncaught Exception: 12: REST API is deprecated for versions v2.1 and higher (12) thrown in /home/content/19/9652219/html/wp-content/plugins/seo-facebook-comments/facebook/base_facebook.php on line 1273