Объединение в пространственные индексы mysql

У меня есть две таблицы: одна с точками, другая с полисами.

CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `point` point NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `ranges` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `poly` polygon NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `poly` (`poly`)
) ENGINE=MyISAM;

Я хочу объединить диапазоны с точками в точках внутри полисов. Запросы выглядят просто:

SELECT * 
  FROM points 
    LEFT JOIN ranges 
      ON MBRCONTAINS(poly, point) 
  WHERE points.id = 2;

Этот запрос работает быстро и использует индексы, часть объяснения:

table  | type  | possible_keys | key  | key_len
ranges | range | poly          | poly | 34

Но, когда я пытаюсь присоединиться к нескольким строкам из таблицы points:

SELECT * 
  FROM points 
   LEFT JOIN ranges 
    ON MBRCONTAINS(poly, point) 
  WHERE points.id IN (1,2,3);

все ломается:

+----+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | points     | range | PRIMARY       | PRIMARY | 4       | NULL |      3 | Using where |
|  1 | SIMPLE      | ranges     | ALL   | poly          | NULL    | NULL    | NULL | 155183 |             |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+

Добавление FORCE INDEX (poly) не помогает.

Пример данных для проверки запросов (извините, только версия php, я не часто встречаюсь с процедурами SQL):

//points
for($i=0;$i<=500;$i++) {
    $point = mt_rand();
    mysql_query('INSERT INTO points (point) VALUES (POINTFROMWKB(POINT('.$point.', 0)))');
}

$qty = 20000;
$max = mt_getrandmax();
$add = $max / $qty
$end = 0;

//polys
while($end < $max) {
    $start = $end;
    $end = mt_rand($start, $start + $add);
    mysql_query('INSERT INTO ranges (poly) VALUES (
        GEOMFROMWKB(POLYGON(LINESTRING(
            POINT('.$start.', -1),
            POINT('.$end.',   -1),
            POINT('.$end.',    1),
            POINT('.$start.',  1),
            POINT('.$start.', -1)
          )))
    )');
}

Ответ 1

Я считаю, что это потому, что MySQL не поддерживает слияние пространственных индексов. Не уверен, что это правда, но я читал это где-то в прошлом. Если у вас есть инструкция OR, то пространственные индексы не используются

В вашем случае, где вы делаете point.id = 1, прямой выбор с одним результатом, который возвращается, который используется в mbrcontains. Это использует индекс.

Когда вы добавляете point.in(1,2,3), который возвращает 3 результата, и каждый из них должен быть отображен в таблицу диапазонов, поэтому не работает

результат

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  points  range   PRIMARY     PRIMARY     4   NULL    3   100.00  Using where
1   SIMPLE  ranges  ALL     poly    NULL    NULL    NULL    6467418     100.00

Вы можете упростить свой тест без таблицы точек, выполнив это: SELECT * FROM диапазоны, где mbrcontains (poly, GEOMFROMWKB (POINT (0, 0)))

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  ranges  range   poly    poly    34  NULL    1   100.00  Using where

И теперь это; SELECT * FROM диапазоны, где mbrcontains (poly, GEOMFROMWKB (POINT (0, 0))) ИЛИ mbrcontains (poly, GEOMFROMWKB (POINT (10, 10)))

результат

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  ranges  ALL     poly    NULL    NULL    NULL    6467418     100.00  Using where

Посмотрите, что во втором случае вы не используете индекс и просто сканирование.

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

EXPLAIN EXTENDED 
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 1
UNION DISTINCT
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 2
UNION DISTINCT
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 3

результат

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY     points  const   PRIMARY     PRIMARY     4   const   1   100.00   
1   PRIMARY     ranges  range   poly    poly    34  NULL    1   100.00  Using where
2   UNION   points  const   PRIMARY     PRIMARY     4   const   1   100.00   
2   UNION   ranges  range   poly    poly    34  NULL    1   100.00  Using where
3   UNION   points  const   PRIMARY     PRIMARY     4   const   1   100.00   
3   UNION   ranges  range   poly    poly    34  NULL    1   100.00  Using where
NULL    UNION RESULT    <union1,2,3>    ALL     NULL    NULL    NULL    NULL    NULL    NULL

Ответ 2

Я успешно использовал похожие запросы, только с одной разницей в модели данных: пространственный ключ в базе данных точек. В моем случае:

CREATE TABLE geopoints (
  pid int(11) NOT NULL AUTO_INCREMENT,
  description varchar(255) NOT NULL DEFAULT '',
  geopoint point NOT NULL,
  PRIMARY KEY (pid),
  SPATIAL KEY geopoint (geopoint) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

И все прошло хорошо в таких запросах:

SELECT pt.pid, x(geopoint), Y(geopoint), pl.pid, AsText(geopolygon) 
  FROM geopoints pt INNER JOIN geopolygons pl ON MBRCONTAINS(geopolygon, geopoint)
 WHERE pt.pid IN (1,2,4,5) AND pl.pid BETWEEN 1 AND 5;

мои два цента,

Ответ 3

Если все, с чем вы имеете дело, это квадраты, я бы просто имел дело с 4 номерами в вашей таблице, которые могут быть проиндексированы с изображением Top, Left, Height, Width, а затем запустите ваш запрос, когда ваша точка имеет свою координату "X" между левой, левой + шириной и координатой "Y" между вершинами, вершиной и высотой.