Как ускорить(оптимизировать) запрос к MySQL?

Question

Я новичок в программировании, поэтому прошу не сильно пинать за вопрос, спрашиваю стараясь

Вопрос следующий:

Есть БД MySQL. Версия клиента базы данных: libmysql - 5.6.43

Есть таблица событий notes_all. Результат запроса SHOW CREATE TABLE notes_all:

CREATE TABLE `notes_all` (
`id` char(26) NOT NULL,
`type` text NOT NULL,
`entity_id` int(11) NOT NULL,
`entity_type` text NOT NULL,
`created_by` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
`value_after` json NOT NULL,
`value_before` json NOT NULL,
`account_id` int(11) NOT NULL,
`_links` json NOT NULL,
`_embedded` json NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_2` (`id`),
KEY `id` (`id`),
KEY `created_at` (`created_at`),
KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf

В конечном итоге я хочу получить метки времени (timestamp) событий, после которых в течении получаса не было никаких событий.

Т.е. в конечном итоге нужно:

created_by | created_at | count_events

где count_events - количество иных событий в течении получаса после этого события.

Идеально, конечно, если будут выбраны только события, в которых count_events равен 0.

Составляю запрос (тут $dateStart - timestamp начала месяца, $dateEnd - timestamp конца месяца):

SELECT `created_by`,`created_at`, (
    SELECT
        COUNT(*)
    FROM
        `notes_all` as n
    WHERE
        n.created_at > notes_all.created_at AND n.created_at < (notes_all.created_at + 30 * 60) AND n.`created_by` = notes_all.created_by
    GROUP BY n.`created_by`
  ) as count_events
FROM
  `notes_all`
WHERE 
  `notes_all`.`created_at` >= ".$dateStart." and 
  `notes_all`.`created_at` <= ".$dateEnd

Получаю:

Получаю полный список всех событий, где в столбце count_events, указано количество событий в течении получаса, следующего за указанным событием. Т.е. получаю почти то, что нужно (за исключением фильтрации только по нулевым событиям в столбце count_events)

Этот запрос всё правильно считает, но он безумно медленный. 25 строк считает в среднем за 4 секунды. Можно ли как-то получить тот же результат, кратно ускорив выполнение запроса?

Answer 1

Попробуйте:

События, после которых в течении получаса не было никаких событий

SELECT t1.*
FROM notes_all t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM notes_all t2
                   WHERE t2.created_at BETWEEN  t1.created_at AND t1.created_at + INTERVAL 30 MINUTE )
-- WHERE t1.created_at BETWEEN @start AND @end

Количество событий в течение 30 минут после события

SELECT t1.*, COUNT(*)
FROM notes_all t1
LEFT JOIN notes_all t2 
    ON t2.created_at BETWEEN  t1.created_at AND t1.created_at + INTERVAL 30 MINUTE
-- WHERE t1.created_at BETWEEN @start AND @end
GROUP BY t1.id

Answer 2

SELECT IF(@prev_by=created_by, @prev_at-created_at, NULL) diff,
       @prev_by:=created_by AS created_by, @prev_at:=created_at
  FROM (SELECT @prev_by:=NULL, @prev_at:=NULL) x, notes_all
 WHERE notes_all.created_at >= $dateStart AND notes_all.created_at <= $dateEnd
 ORDER BY created_by, created_at DESC

Запрос считает разницу во времени между идущими друг за другом событиями по одному пользователю. Если в обрабатывамом окне нет более позних событий дает NULL. Основан на запоминании пользователя и времени события в пременных и использовании их при обработке следующей строки. Поэтому для запроса важна сортировка (по пользователю и дате в обратном порядке).

При необходимости можно обернуть его в внешний запрос и проверить diff на нужную разность select * from (запрос указанный выше) x where diff < 30 * 60

БЛОГ НА HUSL

Как ускорить(оптимизировать) запрос к MySQL?

Ответы (2 шт):