Теоретически, нисходящий индекс не поможет, он полезен, только если вы упорядочиваете по этому столбцу desc. Пространственный индекс является хорошим предложением, но работает только для таблиц MySQL MyISAM.
ользую MySQL DB, и у меня есть следующая таблица:
CREATE TABLE SomeTable (
PrimaryKeyCol BIGINT(20) NOT NULL,
A BIGINT(20) NOT NULL,
FirstX INT(11) NOT NULL,
LastX INT(11) NOT NULL,
P INT(11) NOT NULL,
Y INT(11) NOT NULL,
Z INT(11) NOT NULL,
B BIGINT(20) DEFAULT NULL,
PRIMARY KEY (PrimaryKeyCol),
UNIQUE KEY FirstLastXPriority_Index (FirstX,LastX,P)
) ENGINE=InnoDB;
Таблица содержит 4,3 миллиона строк и никогда не изменяется после инициализации.
Важными столбцами этой таблицы являютсяFirstX, LastX, Y, Z а такжеP.
Как видите, у меня есть уникальный индекс по строкамFirstX, LastX а такжеP.
КолонныFirstX а такжеLastX определить диапазон целых чисел.
Запрос, который мне нужно выполнить для этой таблицы, выбирает для данного X все строки, имеющие FirstX <= X <= LastX (т.е. все строки, чей диапазон содержит входное число X).
Например, если таблица содержит строки (я включаю только соответствующие столбцы):
FirstX LastX P Y Z
------ ------ - --- ---
100000 500000 1 111 222
150000 220000 2 333 444
180000 190000 3 555 666
550000 660000 4 777 888
700000 900000 5 999 111
750000 850000 6 222 333
и мне нужны, например, строки, содержащие значение185000, первый3 строки должны быть возвращены.
Я попробовал запрос, который должен использовать индекс:
SELECT P, Y, Z FROM SomeTable WHERE FirstX <= ? AND LastX >= ? LIMIT 10;
Даже без LIMIT этот запрос должен возвращать небольшое количество записей (меньше чем50) для любого данного X.
Этот запрос был выполнен приложением Java для120000 значения X. К моему удивлению, он взял на себя10 часов (!) и среднее время на запрос было0,3 секунды.
Это не приемлемо, даже близко не приемлемо. Это должно быть намного быстрее.
Я изучил один запрос, который занял0,563 секунды чтобы убедиться, что индекс используется. Запрос, который я пробовал (такой же, как запрос выше, с конкретным целочисленным значением вместо?) вернулся2 ряда.
я использовалEXPLAIN чтобы выяснить, что происходит:
id 1
select_type SIMPLE
table SomeTable
type range
possible_keys FirstLastXPriority_Index
key FirstLastXPriority_Index
key_len 4
ref NULL
rows 2104820
Extra Using index condition
Как вы можете видеть, в исполнении участвуют2104820 строки (почти 50% строк таблицы), хотя только 2 строки удовлетворяют условиям, поэтому половина индекса проверяется, чтобы получить только 2 строки.
Что-то не так с запросом или индексом? Можете ли вы предложить улучшение для запроса или индекса?
РЕДАКТИРОВАТЬ:
В некоторых ответах предлагалось, чтобы я запускал запрос в пакетном режиме для нескольких значений X. Я не могу этого сделать, поскольку я выполняю этот запрос в режиме реального времени, когда входные данные поступают в мое приложение. Каждый раз, когда поступает ввод X, я должен выполнить запрос для X и выполнить некоторую обработку выходных данных запроса.