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)
Subject
Views
Written By
Posted
POINT index is used in v5.7 but not in v8.0
1802
June 11, 2018 07:32AM
1051
June 12, 2018 01:18AM
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.