Spatial Index not being used for polygon-in-bounding-box search

Question

I have a MyISAM table in MariaDB containing two datetime columns begin and end and would like to create and use a spatial index on the two in a similar fashion to the blog post here.

Here is how I create the table:

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL,
  `begin` datetime NOT NULL,
  `end` datetime NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

After filling the table with data, I then add a polygon column:

ALTER TABLE mytable add time_range_int POLYGON NULL;

then fill the column with polygons created from begin and end:

UPDATE mytable
  SET time_range_int=Polygon(
    LineString(
      Point(UNIX_TIMESTAMP(begin), 1),
      Point(UNIX_TIMESTAMP(begin), 0),
      Point(UNIX_TIMESTAMP(end), 0),
      Point(UNIX_TIMESTAMP(end), 1),
      Point(UNIX_TIMESTAMP(begin), 1)
    )
  );

I then set the column to NOT NULL and create a spatial index upon it:

ALTER TABLE mytable MODIFY time_range_int POLYGON NOT NULL;
CREATE SPATIAL INDEX index_time_range on mytable(time_range_int);

Then I try to query the table for rows that contain the timespan between 2016-12-19 and 2016-12-20 which I normally do without the spatial index as follows:

SELECT SQL_NO_CACHE begin, end from mytable WHERE begin<="2016-12-19" and end>="2016-12-20";

Trying to utilize the newly created spatial index by using instead:

SELECT SQL_NO_CACHE begin, end FROM mytable
  WHERE MBRWithin(
    Polygon(
      LineString(
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1)
      )
    ),
    time_range_int,
);

(Thanks O. Jones for the hint concerning order of parameters) However, the spatial index is not being used and both versions of the query require the same execution time. Even though Explain on the second shows that the query is using the index:

+------+---------------+---------+--------+------------------+------------------+-----------+--------+--------+-------------+
|   id | select_type   | table   | type   | possible_keys    | key              |   key_len |    ref |   rows | Extra       |
|------+---------------+---------+--------+------------------+------------------+-----------+--------+--------+-------------|
|    1 | SIMPLE        | mytable | range  | index_time_range | index_time_range |        34 | <null> |  67505 | Using where |
+------+---------------+---------+--------+------------------+------------------+-----------+--------+--------+-------------+

And I notice no speed difference when I run the second query with IGNORE INDEX(index_time_range)

Is this a bug in MariaDB? My Version is 10.1.21-MariaDB or am I missing something?

Is the query cache, maybe, confusing your testing? Do `SELECT NO_QUERY_CACHE begin, end FROM mytable...` Read this. http://stackoverflow.com/questions/181894/mysql-force-not-to-use-cache-for-testing-speed-of-query Also, I wonder if the high degree of y-dimension overlap in your dataset somehow messes up the spatial index search? — O. Jones, May 03 '17 at 12:47
Actually, I am indeed using `SQL_NO_CACHE` as you have suggested, and have already edited the question to include that. For the second part, I don't think I quite get the idea could you please elaborate? — architectonic, May 03 '17 at 13:32

score 1 · Answer 1 · answered May 03 '17 at 10:34

Try switching the order of parameters to MBRContains() something like this.

SELECT begin, end FROM mytable
  WHERE MBRContains(
    Polygon(
      LineString(
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 0),
        Point(UNIX_TIMESTAMP("2016-12-20 00:00:00"), 1),
        Point(UNIX_TIMESTAMP("2016-12-19 00:00:00"), 1)
      )
    ),
    time_range_int);

I've had success, with actual x and y coordinates, with using the spatial search that way.

Thanks, this has changed the result of `EXPLAIN` to include `index_time_range` as an index, however, performance was not affected at all, I will edit the question to include your suggestion — architectonic, May 03 '17 at 10:58

Spatial Index not being used for polygon-in-bounding-box search

1 Answers1

Linked