Блог

У меня есть следующий запрос mysql, который работает правильно, единственная проблема в том, что он действительно медленный. Может ли кто-нибудь подсказать мне, есть ли лучший и быстрый способ выполнить такой запрос??

 SELECT 
(select sum(tot) FROM NOI_per_order_perf_global WHERE DATE(datte) = DATE(NOW()) AND origin_country = 'fr') as yoofr,
(select sum(tot) FROM table1 WHERE DATE(datte) = DATE(NOW()) - INTERVAL 1 DAY AND  contry = 'fr') as yooyestfr, 
(select sum(tot) FROM table1 WHERE yearweek(`datte`, 1) = yearweek(curdate(), 1) AND YEAR(datte)=2014 AND contry = 'fr') as yooyesttfr,
(select sum(tot) FROM table1 WHERE MONTH(datte) = MONTH(CURDATE()) AND YEAR(datte)=2014 AND contry = 'fr') as yooyestttfr,
(select MAX(datte) FROM table1 WHERE contry = 'fr') as timingfr,

(select sum(tot) FROM table1 WHERE DATE(datte) = DATE(NOW()) AND contry = 'de') as yoode,
(select sum(tot) FROM table1 WHERE DATE(datte) = DATE(NOW()) - INTERVAL 1 DAY AND  contry = 'de') as yooyestde, 
(select sum(tot) FROM table1 WHERE yearweek(`datte`, 1) = yearweek(curdate(), 1) AND YEAR(datte)=2014 AND contry = 'de') as yooyesttde,
(select sum(tot) FROM table1 WHERE MONTH(datte) = MONTH(CURDATE()) AND YEAR(datte)=2014 AND contry = 'de') as yooyestttde,
(select MAX(datte) FROM table1 WHERE contry = 'de') as timingde,

(select sum(tot) FROM table1 WHERE DATE(datte) = DATE(NOW()) AND contry = 'it') as yooit,
(select sum(tot) FROM table1 WHERE DATE(datte) = DATE(NOW()) - INTERVAL 1 DAY AND  contry = 'it') as yooyestit, 
(select sum(tot) FROM table1 WHERE yearweek(`datte`, 1) = yearweek(curdate(), 1) AND YEAR(datte)=2014 AND contry = 'it') as yooyesttit,
(select sum(tot) FROM table1 WHERE MONTH(datte) = MONTH(CURDATE()) AND YEAR(datte)=2014 AND contry = 'it') as yooyestttit,
(select MAX(datte) FROM table1 WHERE contry = 'it') as timingit,

(select sum(tot) FROM table1 WHERE DATE(datte) = DATE(NOW()) AND contry = 'es') as yooes,
(select sum(tot) FROM table1 WHERE DATE(datte) = DATE(NOW()) - INTERVAL 1 DAY AND  contry = 'es') as yooyestes, 
(select sum(tot) FROM table1 WHERE yearweek(`datte`, 1) = yearweek(curdate(), 1) AND YEAR(datte)=2014 AND contry = 'es') as yooyesttes,
(select sum(tot) FROM table1 WHERE MONTH(datte) = MONTH(CURDATE()) AND YEAR(datte)=2014 AND contry = 'es') as yooyestttes,
(select MAX(datte) FROM table1 WHERE contry = 'es') as timinges,
  

спасибо, Марко.

Возврат одной строки на округ может быть немного более эффективным:-

 SELECT origin_country, 
        SUM(IF(DATE(datte) = DATE(NOW()), tot, 0)) AS yoofr,
        SUM(IF(DATE(datte) = DATE(NOW()) - INTERVAL 1 DAY, tot, 0)) AS yooyestfr, 
        SUM(IF(yearweek(`datte`, 1) = yearweek(curdate(), 1) AND YEAR(datte)=2014, 0)) AS yooyesttfr,
        SUM(IF(MONTH(datte) = MONTH(CURDATE()) AND YEAR(datte)=2014, 0)) AS yooyestttfr,
        MAX(datte) AS timingfr
FROM table1
WHERE origin_country IN ('fr', 'de', 'it', 'es')
GROUP BY origin_country
  

Но это все равно не будет здорово (индексы не будут так сильно использоваться в полях суммы)

Возможно, это позволит использовать индексы (если они есть полезные):-

 SELECT sub1.origin_country, yoofr, yooyestfr, yooyesttfr, yooyestttfr, timingfr
FROM
(
    SELECT origin_country, sum(tot) as yoofr,
    FROM table1
    WHERE origin_country IN ('fr', 'de', 'it', 'es')
    AND DATE(datte) = DATE(NOW())
    GROUP BY origin_country
) sub1
INNER JOIN
(
    SELECT origin_country, sum(tot) as yooyestfr 
    FROM table1
    WHERE origin_country IN ('fr', 'de', 'it', 'es')
    AND DATE(datte) = DATE(NOW()) - INTERVAL 1 DAY
    GROUP BY origin_country
) sub2 ON sub1.origin_country = sub2.origin_country
INNER JOIN
(
    SELECT origin_country, sum(tot) as yooyesttfr 
    FROM table1
    WHERE origin_country IN ('fr', 'de', 'it', 'es')
    AND yearweek(`datte`, 1) = yearweek(curdate(), 1) AND YEAR(datte)=2014
    GROUP BY origin_country
) sub3 ON sub1.origin_country = sub3.origin_country
INNER JOIN
(
    SELECT origin_country, sum(tot) as yooyestttfr 
    FROM table1
    WHERE origin_country IN ('fr', 'de', 'it', 'es')
    AND MONTH(datte) = MONTH(CURDATE()) AND YEAR(datte)=2014
    GROUP BY origin_country
) sub4 ON sub1.origin_country = sub4.origin_country
INNER JOIN
(
    SELECT origin_country, MAX(datte) as timingfr 
    FROM table1
    WHERE origin_country IN ('fr', 'de', 'it', 'es')
    GROUP BY origin_country
) sub5 ON sub1.origin_country = sub5.origin_country
  

Однако многое из этого будет зависеть от индексов в ваших реальных таблицах