MySQL Forums
Forum List  »  GIS

POINT index is used in v5.7 but not in v8.0
Posted by: J C
Date: June 11, 2018 07:32AM

Dear MySQL Forum users,

I am encountering a problem with using spatial indices. I have a table with 2 rows: id and location. Location is a POINT type, NOT NULL.

The CREATE code is as follows:
******************************
CREATE TABLE `test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`location` POINT NOT NULL,
`location2` GEOMETRY NOT NULL,
INDEX `id` (`id`),
SPATIAL INDEX `location` (`location`),
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=16384
;
******************************

Then, running the following query yields different results in MySQL v5.7 and v8.0:

******************************
EXPLAIN SELECT * FROM test WHERE MBRContains(ST_GeomFromText('LINESTRING(4.848402825923472 51.985867267867974,5.562753957891971 52.205296088730535)', 4326), location) LIMIT 1;
******************************

In v5.7 it works fine and the output is
"1" "SIMPLE" "test" \N "range" "location" "location" "34" \N "1" "100,00" "Using where".

However, in v8.0, the entry for possible_keys is (null). Why is this the case? I would like to use an index to speed up my query.

Thanks in advance!

(Sorry if I posted this in the wrong part of the forum)

Options: ReplyQuote


Subject
Views
Written By
Posted
POINT index is used in v5.7 but not in v8.0
1765
J C
June 11, 2018 07:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.