Как корректно ограничить выборку в БД?

Question

[Евгений Иванов]

Есть БД, а в ней таблица с 10 миллионами записей.
Структура таблицы простая - 4 поля (напишу на всякий случай, хотя это не важно).
id - автоинкремент (ключ)
time - число, юникс время (ключ)
ticker - короткая строка, например A (Может тоже сделать ключем т.к. часто идет выборка?)
value - число с плавающей запятой.
Нужно выбирать данные из таблицы (строки) с конца.

Сейчас есть такой запрос.

SELECT `value` FROM `data` WHERE `ticker`='A' ORDER BY `id` DESC LIMIT 1000

Возникает проблема, если в БД нет 1000 строк где ticker=A.
Соответственно MySQL ищет эти строки по всей БД и это долго. Нужно ограничить этот поиск.

Известно что если в последних 200 записях "выше" (DESC ) от места поиска, нет такого тикера, то и записей с такими тикерами больше нет во всей в таблице.

Нашел тикер в 200 записях снизу? Ищи дальше от этого места в следующих 200. Нашел? Дальше от этого места в следующих 200.
Нет? Стоп.

Как корректно ограничить выборку в БД?
Возможно ли тут справится одним sql запросом?

Или всё же нужно писать несколько sql запросов, по алгоритму описанному выше? Запоминать id, отдельным запросом проверять наличие и т.д.

Answer 1

[Roman Terekhin]

На первый взгляд вам просто нужен составной индекс по ticker и id
CREATE INDEX ticker_id_idx ON data(ticker, id);

Comments

[Евгений Иванов]

К сожалению, я пока не понял куда это вообще пристроить.

[Roman Terekhin]

Евгений Иванов, это в вашу таблицу пристроить :)
С таким составным индексом в запросе

SELECT `value` FROM `data` WHERE `ticker`='A' ORDER BY `id` DESC LIMIT 1000

Mysql будет использовать индекс и для выборки WHERE и для сортировки по id.

[Евгений Иванов]

Немного почитал про индексы.
То есть вы предлагаете перед запросом, выполнить запрос CREATE INDEX ticker_id_idx ON data(ticker, id); что создаст индекс и ускорит дальнейший поиск?

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

И может всё таки стоит сделать 2м ключем поле ticker? Ведь по нему ищем. Это как то конфликтует с индексом?

[Roman Terekhin]

Евгений Иванов, запрос на создание индекса один раз выполняется для таблицы.
Важно учитывать магию оптимизатора запросов, если просто создать индекс для поля ticker, то в вашем запросе он использоваться не будет, mysql отсортирует таблицу по id (т.к ORDER BY приоритетнее) и начнет fullscan по полю ticker. В запросе по таблице может участвовать только один индекс.
Как раз для решения этой проблемы есть составные индексы, и в них важен порядок полей.
тут хорошо описано.