dummy2002,
ivnik,
mib
Спасибо, коллеги, ваши ответы подтолкнули мои мысли в правильном направлении. Если коротко, то идея состоит в создании временных таблиц, содержащих только нужный набор данных. До меня, вдруг, дошло, что мои тысячи случайных экспериментов можно очень красиво сгруппировать по времени события, т.к. они очень часто происходят в течении одной и той же минуты, это существенно снижает вычислительную нагрузку на БД. В итоге, удалось добиться снижения времени обработки с 5 минут, до 5 сек. :)
Вот работающий код, буду признателен за критику и советы:
-- После получения очередных суточных данных формируем временную таблицу, в которую попадают только те данные, которые попадают в диапазон необработанных случайных экспериментов
SELECT @minRandomTimestamp := ( SELECT event_timestamp FROM random_events ORDER BY event_timestamp ASC LIMIT 1 );
DROP TABLE IF EXISTS experiment_tmp;
CREATE TEMPORARY TABLE experiment_tmp AS
( -- Одна (старшая) запись из экспериментов за предыдущие сутки
SELECT event_timestamp, ex_value
FROM experiments
WHERE event_timestamp < @minRandomTimestamp
ORDER BY event_timestamp DESC
LIMIT 1
)
UNION
( -- Все новые
SELECT event_timestamp, ex_value
FROM experiments
WHERE event_timestamp >= @minRandomTimestamp
ORDER BY event_timestamp ASC
)
;
CREATE UNIQUE INDEX event_timestamp ON experiment_tmp (event_timestamp);
-- Определяем время последнего известного эксперимента
SELECT @maxExperimentTimestamp := ( SELECT event_timestamp FROM experiment_tmp ORDER BY event_timestamp DESC LIMIT 1 );
-- Выбираем во временную таблицу УНИКАЛЬНЫЕ (по времени) события, для которых появились суточные данные. Записей в этой таблице будет не больше, чем минут в сутках
DROP TABLE IF EXISTS random_events_tmp;
CREATE TEMPORARY TABLE random_events_tmp AS
SELECT event_timestamp, 0 prev_value_timestamp, 00000000000.0000 prev_value, 0 next_value_timestamp, 00000000000.0000 next_value
FROM random_events
WHERE event_timestamp <= @maxExperimentTimestamp
GROUP BY event_timestamp
;
CREATE UNIQUE INDEX pt ON random_events_tmp ( event_timestamp );
-- Вычисляем время предыдущего эксперимента
UPDATE random_events_tmp tr
SET tr.prev_value_timestamp = (
SELECT event_timestamp
FROM experiment_tmp te
WHERE te.event_timestamp <= tr.event_timestamp
ORDER BY te.event_timestamp DESC
LIMIT 1
);
-- Проставляем величину предыдущего эксперимента
UPDATE random_events_tmp tr
LEFT JOIN experiment_tmp te ON te.event_timestamp = tr.prev_value_timestamp
SET tr.prev_value = te.ex_value;
-- Вычисляем время следующего эксперимента
UPDATE random_events_tmp tr
SET tr.next_value_timestamp = (
SELECT event_timestamp
FROM experiment_tmp te
WHERE te.event_timestamp > tr.event_timestamp
ORDER BY te.event_timestamp ASC
LIMIT 1
);
-- Проставляем величину следующего эксперимента
UPDATE random_events_tmp tr
LEFT JOIN experiment_tmp te ON te.event_timestamp = tr.next_value_timestamp
SET tr.next_value = te.ex_value;
-- Переносим данные в таблицу случайных экспериментов. Это самый долгий запрос > 3 сек.
UPDATE random_events f
LEFT JOIN random_events_tmp tr ON f.event_timestamp = tr.event_timestamp
SET
f.prev_value_timestamp = tr.prev_value_timestamp
, f.next_value_timestamp = tr.next_value_timestamp
, f.prev_value = tr.prev_value
, f.next_value = tr.next_value
;
-- После этого происходит обработка данных на клиенте и обработанные записи удаляются из таблицы random_events
