MySQL Bugs: #83892: Spatial queries with multiple spatial predicates do not use spatial index

Bug #83892	Spatial queries with multiple spatial predicates do not use spatial index
Submitted:	19 Nov 2016 22:22	Modified:	9 Feb 2018 12:13
Reporter:	Nikolaos Priggouris	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S5 (Performance)
Version:	5.7.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	multiple spatial predicates, slow, spatial index

Description:
A spatial query involving multiple spatial predicates (ST_Intersects) executes extremely slower compared to executing each one separately using a sinlge predicate. It seems that for some reason the use of spatial index is cancelled when the query involves more than 2 predicates.

The problem was reported in Mysql 5.7.9 and replicated in MySQL 5.7.10 and 5.7.15 therfore I assume it exists in MySQL 5.7 in general.  

How to repeat:
Execute the  following ddl statements

CREATE SCHEMA `test_spatial` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE test_spatial.`test_grid` (
  `FID` bigint(20) NOT NULL AUTO_INCREMENT,
  `the_geom` multipolygon NOT NULL,
  PRIMARY KEY (`FID`),
  SPATIAL KEY `index2` (`the_geom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

then unzip and import the data provided in the attached file (test_spatial.zip) using Mysql workbench (contains around 200.000 rows).

-- Execute a query with multiple spatial predicates OR together

SELECT FID, ST_AsText(the_geom) as the_geom FROM `test_spatial`.`test_grid` WHERE  (ST_Intersects(the_geom, GeomFromText('POLYGON ((483711.8127301318 4197090.13317122, 483711.8127301318 4197125.039515583, 483740.4671919224 4197125.039515583, 483740.4671919224 4197090.13317122, 483711.8127301318 4197090.13317122))', 2100)) OR (ST_Intersects(the_geom, GeomFromText('POLYGON ((483855.08503908495 4197249.556176818, 483855.08503908495 4197298.008266755, 483888.94940301933 4197298.008266755, 483888.94940301933 4197249.556176818, 483855.08503908495 4197249.556176818))', 2100)) OR (ST_Intersects(the_geom, GeomFromText('POLYGON ((483866.0258335868 4197122.955554726, 483866.0258335868 4197194.331214095, 483909.26802137995 4197194.331214095, 483909.26802137995 4197122.955554726, 483866.0258335868 4197122.955554726))', 2100)) OR ST_Intersects(the_geom, GeomFromText('POLYGON ((483722.23253441934 4197249.035186604, 483722.23253441934 4197315.2009438295, 483771.72660478496 4197315.2009438295, 483771.72660478496 4197249.035186604, 483722.23253441934 4197249.035186604))', 2100)))))

-- Execute with separate predicates

SELECT FID, ST_AsText(the_geom) as the_geom FROM `test_spatial`.`test_grid` WHERE  (ST_Intersects(the_geom, GeomFromText('POLYGON ((483711.8127301318 4197090.13317122, 483711.8127301318 4197125.039515583, 483740.4671919224 4197125.039515583, 483740.4671919224 4197090.13317122, 483711.8127301318 4197090.13317122))', 2100)));

SELECT FID, ST_AsText(the_geom) as the_geom FROM `test_spatial`.`test_grid` WHERE  (ST_Intersects(the_geom, GeomFromText('POLYGON ((483855.08503908495 4197249.556176818, 483855.08503908495 4197298.008266755, 483888.94940301933 4197298.008266755, 483888.94940301933 4197249.556176818, 483855.08503908495 4197249.556176818))', 2100)));

SELECT FID, ST_AsText(the_geom) as the_geom FROM `test_spatial`.`test_grid` WHERE   (ST_Intersects(the_geom, GeomFromText('POLYGON ((483866.0258335868 4197122.955554726, 483866.0258335868 4197194.331214095, 483909.26802137995 4197194.331214095, 483909.26802137995 4197122.955554726, 483866.0258335868 4197122.955554726))', 2100)));

SELECT FID, ST_AsText(the_geom) as the_geom FROM `test_spatial`.`test_grid` WHERE  (ST_Intersects(the_geom, GeomFromText('POLYGON ((483722.23253441934 4197249.035186604, 483722.23253441934 4197315.2009438295, 483771.72660478496 4197315.2009438295, 483771.72660478496 4197249.035186604, 483722.23253441934 4197249.035186604))', 2100)));

Observe that while the single predicate statements execute almost instantly, the combined one needs around 15 sec in order to complete.
While the number of rows increases the  response times increae also indicating that indeed it has to do with the fact the index is not used.

spatial data table to be used for replicating the problem

Attachment: test_spatial.zip (application/zip, text), 2.31 MiB.

Hi,

I have downloaded your ZIP file and I have fed the .sql file into mysql client and got this error:

ERROR 1416 (22003) at line 41: Cannot get geometry object from data you send to the GEOMETRY field

Hence, your dump has an error in the GIS columns data.

corrected spatial data table

Attachment: test_spatial.zip (application/zip, text), 2.57 MiB.

Hi Sinisa,

Sorry for the corrupted zip. I have uploaded a new one with the same name which should be OK (the problem was due to a mismatch in the geometry types since the exported data are of type polygon and not multipolygon)

What you reported is quite true. I repeated your behavior.  Hence, I verify this bug. However, since there is an easy workaround, this bug will get a low priority. The workaround is visible in the file that I am attaching.

Verified.

Results from the session in which I repeated the test case and provided an easy workaround.

Attachment: bug83892.txt (text/plain), 34.50 KiB.

Posted by developer:
 
Duplicate of bug#45776.

Hi, 

Since this seems to be quite a long standing issue (based on the fact that it is somewhat duplicate of bug#45776) do we have an indication whether it is going to be fixed or not?

Duplicate of bug#45776.