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
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.
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
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
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.