Mysql, Complex Where предложение замедляет запрос

Question

Mysql, Complex Where предложение замедляет запрос

У меня есть таблица карт, которая структурирована следующим образом:

CREATE TABLE `map` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`occupied` tinyint(2) NOT NULL DEFAULT '0',
`c_type` tinyint(4) NOT NULL DEFAULT '0',
`x` int(11) NOT NULL,
`y` int(11) NOT NULL,
`terrain` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `get_map_w_radius` (`x`,`y`,`id`,`terrain`,`occupied`,`c_type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4_general_ci

Есть 40k записей с x и y от 1 до 200.

И в моем сценарии я использую это так:

SELECT id, terrain, occupied, c_type FROM map WHERE x >= $x-$radius AND x <= $x+$radius AND y >= $y-$radius AND y <= $y+$radius LIMIT 30

Например, $ x равен 15, а y равен 95, а радиус равен 5.
Когда я профилирую запрос, отправка данных составляет 0,000496 миллисекунд, но без учета индекса (только вместо x и y) он выполняется быстрее, хотя в теории это должно быть иначе?
С индексом покрытия, когда я делаю запрос на выборку с простым предложением where, используя x и y только один раз:

SELECT id, terrain, c_type, occupied FROM map WHERE x >= $x And y <= $y limit 30;

Он выполняется намного быстрее, отправляя данные только за 0,000059.
Есть ли что-то, что я пропускаю или неправильно понимаю? Может быть, так должно быть?

0

latitude-longitude mysql performance php

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Для простых 40K строк, предлагаем добавить

INDEX(x),
INDEX(y)

Таким образом, Оптимизатор может посмотреть на BETWEENs и выбрать один thw может работать лучше и сократить работу немного.

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

(Терминологический спор) «Радиус» подразумевает двумерное «расстояние». То, что у вас есть, это «ограничивающая рамка».

Без ORDER BYзапрос вернет любой 30 строк, не обязательно ближайших 30. Если вас это устраивает, хорошо, так как это быстрее.

«Индекс покрытия» против INDEX(x) — У меня есть правило: не создавайте индекс более 5 столбцов. В этом нет ничего плохого, это становится громоздким. Мое предложение также иметь INDEX(y) основан на предположении, что y иногда лучший фильтр.

Остерегайтесь кеша запросов — если он включен, это может быть связано с более «быстрым» выполнением. Запустите время с SELECT SQL_NO_CACHE ... чтобы получить честные сравнения.

Ваш 6-значный индекс УНИКАЛЬНЫЙ? Если так, сделайте это ПК, и полностью избавиться от id,

Если x а также y всегда 0..200, тогда сделайте их TINYINT UNSIGNED (диапазон 0, 255 и 1 байт вместо 4).

Все ароматы ассортимента (BETWEEN, <= — 2-сторонние или 1-сторонние) выполняются одинаково. Так что любые различия в производительности являются артефактами других вещей …

разверните BTree до начального значения (которое может быть началом таблицы)
сканировать вперед
остановиться, если LIMIT достиг и нет ORDER BY
остановка на конечном значении (или конце таблицы)

1