Есть ли способ оптимизировать запрос MySQL, который запускает функцию в каждой строке?

У меня есть запрос MySQL, который извлекает длинную длину из базы данных на основе критерия, проверяет, находятся ли эти точки внутри многоугольника, и возвращает точки, находящиеся внутри многоугольника.

Все работает нормально. Проблема в том, что запрос занимает ок. 20 секунд, чтобы вернуть результат. Есть ли способ оптимизировать этот запрос, чтобы скорость запроса была выше?

SELECT latitude, longitude
FROM myTable
WHERE offense = 'green' AND myWithin(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON(( ...bunch of lat longs...))' )
) = 1;

Я запустил EXPLAIN SELECT..., который произвел

идентификатор | тип_выбора | стол | тип | возможные_ключи | ключ | key_len | ссылка | строки | Дополнительный

1 SIMPLE myTable ALL NULL NULL NULL NULL 137003 Где используется

Есть ли способ оптимизировать запрос, который выполняется на каждой широте и долготе в БД, или это настолько хорошо, насколько это возможно?

Я думаю о том, чтобы сделать выборку в другой таблице, а затем запросить таблицу результатов, но я надеялся, что будет способ улучшить производительность этого запроса.

Если у кого-то есть какие-либо предложения или идеи, я хотел бы услышать их.

Спасибо,

Лакшмиди


mysql query-optimization
person Laxmidi    schedule 21.06.2010    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Насколько велики полигоны? Вы можете определить «ограничивающий прямоугольник» вокруг всего многоугольника, а затем выполнить:

SELECT latitude, longitude
FROM myTable
WHERE
  offense = 'green' AND
  latitude BETWEEN rect_left AND rect_right AND
  longitude BETWEEN rect_top AND rect_bottom AND
  myWithin(
    POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ),
    POLYFROMTEXT( 'POLYGON(( ...bunch of lat longs...))' )) = 1;

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

person Dean Harding    schedule 21.06.2010
comment
Привет кодека, Полигоны - это окрестности. И они неправильной формы. Есть ли способ запустить MBRContains и функции myWithin? Насколько я понимаю, MBRContains дает ложные срабатывания. Дает ли он ложноотрицательные результаты? Если нет, возможно, это было бы решением. Если есть способ использовать MBRContains вместе с функцией myWithin, то мне не нужно добавлять столбцы для ограничивающих прямоугольников для каждого соседства. Люблю слышать ваши мысли. Я думаю, что ограничивающий прямоугольник — хорошая идея. - person Laxmidi; 22.06.2010
comment
Вам не нужно добавлять столбцы для ограничивающего прямоугольника, он легко вычисляется. Просто возьмите свою связку широт и вычислите минимальную и максимальную широту - это 'rect_left' и 'rect_right' соответственно. Минимальная и максимальная долгота из набора долгот широты равна 'rect_bottom' и 'rect_top' соответственно. Вы можете просто рассчитать это перед выполнением запроса. - person Dean Harding; 22.06.2010
comment
Привет Дин, Спасибо за сообщение. Очень хороший момент. Я не думал об этом. Задача решена. Спасибо большое. - person Laxmidi; 24.06.2010

arrow_upward
0
arrow_downward

Я вижу два очевидных направления для оптимизации:

  • Уменьшите результирующий набор больше, прежде чем запускать свою функцию O (n) раз. Прямо сейчас вы запускаете функцию 137003 раза — этого практически не избежать, если вы не можете дальше фильтровать результирующий набор.

  • Сделайте функцию быстрее, чтобы вы по-прежнему запускали ее 137 000 раз, но каждый вызов занимал меньше времени, тем самым сокращая общее время выполнения.

Прямо сейчас ваша функция выполняет 0,1459 миллисекунды на строку, что на самом деле неплохо. Вы, вероятно, захотите попытаться найти какой-то способ еще больше уменьшить количество строк, на которых вы должны запустить его. Сокращение результирующего набора с помощью разумного использования WHERE также имеет дополнительное преимущество, позволяя вашей базе данных выполнять некоторую оптимизацию для вас, как вы и хотите ее использовать.

person Nick Bastin    schedule 21.06.2010
comment
Привет, Ник. Спасибо за сообщение. Я рад, что моя функция работает достаточно быстро. Мне придется поработать над уменьшением количества запусков функции. Я действительно ценю твою помощь. - person Laxmidi; 22.06.2010