Mysql – Spatial Query using MySQL 8.0

MySQLmysql-8.0spatial

I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like

PID, SurveyID, GeoPoint

Basically this is a summarize table where PID and SurveyID makes primary key for table. We do a survey on Animals and this summarize table record which Animal is surveyed at given Geo location on map.

My Index was on column GeoPoint and PID. This table store around 400K record which is summarise from main table that has 2M record for faster execution.

Now, we are planning to upgrade to MySQL 8.0 and we found that it doesn't support Spatial column been index with non-spatial column, so our index was not getting created. This results in our Query now take 8 second instead of 0.6 seconds from older version of MySQL.

More over when I create index only on Spatial Column geopoint, ST_Contains, and MBRContains do not use that Index. Basically our query is as simple as we allow user to draw a Square on Google map and then we use those coordinate to find all Animals in that region.

I am not sure how to solve this issue, as no documentation is found by me for it that help tweaking the query.

Query:

SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID

Table:

CREATE TABLE locationsummary (
 PID bigint(20) NOT NULL,
 SurveyID bigint(20) NOT NULL,
 Sitelat float NOT NULL,
 sitelong float NOT NULL,
 geopoint point NOT NULL,
 PRIMARY KEY (`PID`,`SurveyID`),
 SPATIAL KEY `idx_geopoint` (`geopoint`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT

enter image description here

Edit: 12th July 2019
Today I try to rerun queries and it is now executing in 2 seconds, still not good though, but some how it become better from last run.

Best Answer

SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID

This is slow only because ST_Contains in MySQL does not now, nor has it ever used an index. You have to use MbrContains for that. And you could never put anything on a spatial index, except spatial data.

I see you state in your question MBRContains do not use that Index. I don't believe you. ;)

Note, in PostGIS, ST_Contains will use an index. And, moreover, you can create a spatial index and add to that index non-spatial data (thanks to btree_gist, and btree_gin).

Related Question