Оптимизация долгого запроса к большой таблице с фильтром по датам и времени

Общеизвестно, что чем больше таблицы, тем дольше выполняются запросы к ним.

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

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

Исходные данные для нашего примера:

1. huge_table — наша целевая таблица размером 100 гигабайт
2. join_table1, join_table2 — второстепенные таблицы из которые получаем дополнительные данные. Небольшие (гигабайт или около того)

Запрос выглядит так

SELECT huge_table.*
FROM huge_table
LEFT JOIN join_table1 on huge_table.column1 = join_table1.id
LEFT JOIN join_table2 on huge_table.column2 = join_table2.id
WHERE huge_table.date BETWEEN "2023-01-01 00:00:00" AND "2024-01-01 00:00:00";

Т.е. забираем данные по таблице huge_table за 2023й год

так как таблицы join_table1, join_table2 по условия небольшие, для оптимизации, мы можем рассмотреть упрощенный запрос примерно с тем же временем выполнения:

SELECT *
FROM huge_table
WHERE date BETWEEN "2023-01-01 00:00:00" AND "2024-01-01 00:00:00";

Реальное время выполнения для таблиц в 100 гигов будет несколько минут.

Теперь, представим, что нам нужно найти id первой записи за 2023й год и первой записи за 2024й в той же таблице. Оптимальные запросы будут такие:

SELECT id FROM huge_table WHERE date >= "2023-01-01 00:00:00" ORDER BY id ASC LIMIT 1;

SELECT id FROM huge_table WHERE date <= "2024-01-01 00:00:00" ORDER BY id DESC LIMIT 1;

Оба запроса в сумме выполняются до 5-10 секунд

Теперь понятно что делать с нашим исходным запросом:

SELECT *
FROM huge_table
WHERE date BETWEEN 
     (SELECT id FROM huge_table WHERE date >= "2023-01-01 00:00:00" ORDER BY id ASC LIMIT 1) 
AND 
     (SELECT id FROM huge_table WHERE date <= "2024-01-01 00:00:00" ORDER BY id DESC LIMIT 1);

выполняется на 2 порядка быстрее чем исходный запрос, в итоге.