MySQL индексы для поиска по диапазонам дат

Question

Есть таблица для хранения диапазонов дат и диапазонов цен. Назначение - хранение скидок или бонусов для заказов в привязке к месяцам и диапазонам стоимостей заказов.

CREATE TABLE `ranges` (
  `range_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `range_month_from` date NOT NULL, /* месяц от */
  `range_month_to` date NOT NULL, /* месяц до */
  `range_order_price_from` int(10) unsigned NOT NULL, /* цена заказа от */
  `range_order_price_to` int(10) unsigned NOT NULL, /* цена заказа до */
  PRIMARY KEY (`order_price_range_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Нужно для определенного месяца и определённой цены заказа получить строку из таблицы ranges (range_id). Пример запроса для даты '2025-07-01' и стоимости заказ в 10000:

SELECT
  r.range_id
FROM 
  ranges AS r
WHERE
  r.range_month_from <= '2025-07-01' AND
  r.range_month_to >= '2025-07-01' AND
  r.range_order_price_from <= 10000 AND
  r.range_order_price_to >= 10000
LIMIT
  1;

Количество записей в таблице планируется не большое 20-50 шт. Но эта таблица будет джойниться к большим логам заказов.

Пример лога:

CREATE TABLE `orders` (
  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_month` date NOT NULL, /* месяц */
  `order_price` int(10) unsigned NOT NULL, /* цена заказа */  
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Записей в таблице orders много.

Запросы будут примерно такие:

SELECT
  ...
FROM
  (SELECT
    o.order_price,
    COALESCE(r.range_id, '') AS range_id 
  FROM 
    orders AS o
    LEFT JOIN ranges AS r ON
        o.order_month >= r.range_month_from AND
        o.order_month <= r.range_month_to AND
        o.order_price >= r.range_order_price_from AND
        o.order_price <= r.range_order_price_to
  WHERE
    ...) AS t
  ...;

В реальности таблиц в запросе больше. Хотелось бы чтобы джойн лога к ranges был по индексам 1 к 1.

Вопросы:

Какие индексы для таблицы ranges посоветуете?
Есть ли другие способы хранения диапазонов дат для быстрой выборки? Вариант считать заранее и писать в лог заказов не подходит.

Спасибо!

Answer 1

На вопрос из заголовка отвечает капитан Очевидность: индекс для поиска по диапазону дат надо строить по полям, содержащим даты диапазона.

На вопрос про "другие способы хранения", а так же в целом на вопросы про ускорение никогда не выполнявшихся запросов отвечает Дональд Кнут: "Преждевременная оптимизация - корень всех зол". Вот если возникнут проблемы с поиском по таблице из 50 записей - тогда и надо начинать искать эти способы, добавив вопрос всю диагностику и результаты профайлинга.

Answer 2

Можно попробовать несколько упростить хранение диапазонов (дата, цена).
Если считать, что в диапазонах дат и в диапазонах цен нет разрывов, можно хранить только начало интервала по дате и цене.
В запросе искать в обратном порядке первый меньший или равный диапазон по дате и цене.

Посмотрите пример запроса и модель:

создаем составной индекс (возможно индекс будет лучше с порядком desc), покрывающий запрос

create index ix_month_price_ranges on ranges(range_month_from,range_order_price_from);

Заполнение модели данными можно посмотреть по ссылке (в модели 1000 диапазонов и 3000 ордеров).

Конкретный диапазон (любой его параметр - ид, цена ...) выбираем подзапросом с ограничением limit 1.

Запрос и план выполнения к нему

EXPLAIN ANALYZE 
SELECT *
  , (select range_id -- range_order_price_from 
     from ranges 
     where range_month_from<=order_month and range_order_price_from<=order_price
     order by range_month_from desc,range_order_price_from desc limit 1
     ) AS range_id 
FROM orders AS o

EXPLAIN

-> Table scan on o (cost=300 rows=3000) (actual time=0.0462..1.23 rows=3000 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Limit: 1 row(s) (cost=261e-6 rows=0.111) (actual time=0.396..0.396 rows=1 loops=3000)
-> Filter: ((ranges.range_month_from <= o.order_month) and (ranges.range_order_price_from < o.order_price)) (cost=261e-6 rows=0.111) (actual time=0.396..0.396 rows=1 loops=3000)
-> Covering index scan on ranges using ix_month_price_ranges (reverse) (cost=261e-6 rows=1) (actual time=0.00238..0.325 rows=762 loops=3000)

fiddle

БЛОГ НА HUSL

MySQL индексы для поиска по диапазонам дат

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