0

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.

  • 1
    I don't understand where the problem is. Please provide SHOW CREATE TABLE and the SELECT in question, plus EXPLAIN FORMAT=JSON SELECT ... – Rick James Jun 1 at 1:58
  • @RickJames I add them, hope it help. – Sumit Gupta Jun 7 at 6:04
  • Please file a bug at bugs.mysql.com - Be sure to include the EXPLAIN for both MyISAM and InnoDB. – Rick James Jun 7 at 12:43
  • @RickJames I am bit surprise, so you are saying it should work faster and it maybe some bug in mysql? I was in impression that I did something wrong. – Sumit Gupta Jun 8 at 7:45
  • Why LineString + ST_Envelope instead of Polygon? – Rick James Jun 8 at 13:02
0
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).

  • I write SELECT PID, count(distinct SurveyID) as totalsurvey FROM locationsummary where MBRContains(ST_GeomFromText( 'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)'), geopoint ) group by PID it took same time as with ST_Contains and when use Explain it shows "using where clause", no mention of what index been used. – Sumit Gupta Jul 12 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.