Оптимизация MySQL - WHERE и ORDER BY используют разные столбцы

У меня есть таблица 115MByte, 1600 000 строк, в которой хранятся цены на жилье и координаты. Запросы "SELECT" занимают слишком много времени. Мне было интересно, может ли кто-нибудь предложить способ ускорить его.

Немного упрощенная версия моей таблицы....

CREATE TABLE `prices1` (
 `price` INT(10) NOT NULL,
 `address` VARCHAR(127) NOT NULL,
 `lat` FLOAT(6,4) NOT NULL COMMENT 'GPS latitude',
 `lng` FLOAT(6,4) NOT NULL COMMENT 'GPS longitude',
 INDEX `lat` (`lat`),
 INDEX `price` (`price`)
) ENGINE=MyISAM ROW_FORMAT=DEFAULT

Этот запрос извлекает записи в пределах квадратной области 2 мили x 2 мили....

SELECT * FROM prices1
WHERE lat >= 55.9430 AND lat <= 55.9641
AND lng >= -3.2279 AND lng <= -3.1901 
AND price >= 100000 and price <= 400000
ORDER BY price asc
LIMIT 50

... требуется 0,8 секунды. EXPLAIN говорит, что использует индекс "цена".

Если я использую force index(lat), запрос выполняется за 0,1 секунды - намного быстрее, но мне бы очень хотелось еще быстрее.

Запрос будет запущен через веб-сайт с несколькими пользователями. Все запросы будут похожи, но с разными диапазонами для lat, lng и цены. Скорость записи стола не важна.

Может кто подскажет, как я могу ускорить запрос? Или, по крайней мере, поощряйте MySQL использовать индекс "lat", когда его лучший выбор (я бы предпочел не использовать "force lat", поскольку я обнаружил, что запросы, извлекаемые из большей области, лучше запускаются с использованием индекса "цена" ).

Я запускал "ANALYZE" и "OPTOMIZE".

Я прочитал MySQL по порядку путем индексации '- где тот же индекс может использоваться для предложений WHERE и ORDER BY, но я не думаю, что это можно использовать, когда WHERE является диапазоном?

Стоит ли проводить "пространственные индексы"?

Любые идеи приветствуются, поскольку мне действительно нужно это ускорить быстрее.

1 ответ

Что произойдет, если вы выполните:

INSERT INTO temporal
SELECT * FROM prices1
WHERE lat >= 55.9430 AND lat <= 55.9641
AND lng >= -3.2279 AND lng <= -3.1901 
AND price >= 100000 and price <= 400000
SELECT * from temporal
ORDER BY price asc
LIMIT 50

licensed under cc by-sa 3.0 with attribution.