Как оптимизировать запрос?

Question

[Герман Мартин]

Есть запрос:

select ( select SUM(e.summ)
        from myevents.event as e
          LEFT JOIN myevents.type as t on t.id = e.type
        WHERE (e.dtr >= '2018-04-01') AND (e.dtr <= '2018-04-31') AND
              (t.id = 1))  as 'dohod',
        (select SUM(e.summ)
        from myevents.event as e
          LEFT JOIN myevents.type as t on t.id = e.type
        WHERE (e.dtr >= '2018-04-01') AND (e.dtr <= '2018-04-31') AND
              (t.id = 2)) as 'rashod';

select ( select SUM(e.summ)
         from myevents.event as e
           LEFT JOIN myevents.type as t on t.id = e.type
         WHERE (e.dtr >= '2018-05-01') AND (e.dtr <= '2018-05-31') AND
               (t.id = 1))  as 'dohod',
       (select SUM(e.summ)
        from myevents.event as e
          LEFT JOIN myevents.type as t on t.id = e.type
        WHERE (e.dtr >= '2018-05-01') AND (e.dtr <= '2018-05-31') AND
              (t.id = 2)) as 'rashod';

Он находит сумму доходов и расходов за 1 месяц, точнее первый запрос находит. Второй запрос делает то же самое, но за следующий месяц. Интересуют пути оптимизации подобных запрос. Скорее всего есть способы, где можно 1 запросом получить те же данные за несколько месяцев. Нужна наводка

И можно ли как нибудь избавиться от вложенных запросов, чтобы получить то же самое?
Любые направления на хорошие ссылки и/или книги (или другое) для изучения подобных вопросов приветствуются

Answer 1

[Дмитрий Энтелис]

как то это прям уныло.

1. Вы джойните таблицу type по id и тут же по нему фильтруете - фильтруйте сразу по event.type, таблица type вообще не нужна

2. Если нужно за несколько месяцев, то с точки зрения быстродействия, самое быстрое решение выборка

select DATE_FORMAT(event .dtr,"%Y-%m"), event.type, sum(event.summ),
from event 
where event  dtr between ... and ...
group by DATE_FORMAT(event .dtr,"%Y-%m"), event.type, sum(event.summ)

и дальше на любом ЯП это обрабатывать.

Comments

[Герман Мартин]

спасибо за наводку, так и думал что без ЯП не нарисовать)

[Сергей Соколов]

Герман Мартин, ещё как нарисовать! Только SQL, только hardcore! )

Внутренние оптимизации MySQL быстрее, чем пост-обработка во вне.

[Дмитрий Энтелис]

Сергей Соколов,нет, не быстрее.

[Герман Мартин]

заработало вот так:

select year(event.dtr),month(event.dtr), event.type, sum(event.summ)
from event
where event.type in (1,2,3,4)
group by month(event.dtr), event.type

с DATE_FORMAT почему то не заработал и пришлось убрать sum(event.summ) с группировки

благодарю за качественные ответы!

[Дмитрий Энтелис]

Герман Мартин, я забыл аргумент написать в запросе, обновил ответ.
тот запрос что вы написали - некорректен, нужно как минимум добавить year в group by

[Герман Мартин]

Дмитрий Энтелис, спасибо, уже получилось все

select year(event.dtr) dtr,month(event.dtr) mnth, monthname(dtr) mnthnm, event.type tp, sum(event.summ) sm, type.name nm, type.color cl
from event
  left JOIN type on type.id = event.type
where event.type in (1,2,3,4)
group by mnth, tp
ORDER BY dtr, mnth, tp;

использую приведенный выше код, решил задачу, далее пару часов обработал результаты запроса занес результаты в highcharts и получил результат.

еще раз спасибо за помощь!

upd.: использовал обновленный вариант в шапке в консоли, но задача уже решена, придется в следующий раз, благо записей пока меньше 200

[Дмитрий Энтелис]

Герман Мартин, не теряйте год из group by - иначе у вас в выдаче значение года будет рандомное

Answer 2

[Сергей Соколов]

Не проверял, но что-то типа такого можно сделать:

SELECT
	SUM( CASE WHEN `type` = 1 THEN `summ` END) 'dohod', 
	SUM( CASE WHEN `type` = 2 THEN `summ` END) 'rashod',
	MONTH(dtr) 'month'
FROM myevents.event
WHERE 
  YEAR(dtr) = 2018
  AND `month` BETWEEN 4 AND 5
GROUP BY `month`

Читать документацию MySQL:

• BETWEEN
  
• YEAR() и MONTH() – date and time functions
  
• CASE
  
  

Comments

[Герман Мартин]

спасибо за наводку, пошел читать мануалы...

[Дмитрий Энтелис]

YEAR(dtr) = 2018
  AND `month` BETWEEN 4 AND 5

наглядно конечно, но я бы расстреливал.
запрос не будет использовать индексы от слова вообще.

[Сергей Соколов]

Дмитрий Энтелис, преждевременной оптимизацией лучше не страдать, это лечится )

[Дмитрий Энтелис]

Сергей Соколов, это не преждевременная оптимизация.
Это принципиальное не понимание как работает sql вообще, без обид.
Ну и как бы автор спрашивает - как оптимизировать запрос.

[Сергей Соколов]

Чтобы использовать индексы, их надо создать. В том числе по значению год-месяц.
Темы индексов в рамках данного вопроса даже касаться не надо имхо. Рано.

[Дмитрий Энтелис]

Сергей Соколов, когда вы пишите YEAR(dtr) = 2018 and MONTH(dtr) between ... это означает что для всех записей таблицы будет вычислено значений функций YEAR и MONTH, и только после этого данные будут отфильтрованы.

[Сергей Соколов]

Дмитрий Энтелис, йа-йаа