MySQL Bugs: #96269: st_intersects mysql 8 does not use index

Bug #96269	st_intersects mysql 8 does not use index
Submitted:	22 Jul 2019 13:10	Modified:	25 Jul 2019 13:30
Reporter:	Pavel Dvořák	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	8.0.16	OS:	Debian (9)
Assigned to:		CPU Architecture:	x86 (x64)

Description:
Query appears to NOT use spatial indexes when using st_intersects and SRID
Query took 6.6405 seconds, much faser in 5.7

Explain shows key = polygon

table attached

How to repeat:
Run this query:
SELECT lb.* FROM `gps`.`land_block` lb WHERE st_intersects( ST_GeomFromText('POINT (50.181978 14.591256)', 4326) , lb.polygon )

CREATE TABLE `land_block` (
 `id` int(11) unsigned NOT NULL,
 `land_block_owner_id` int(11) unsigned NOT NULL,
 `company_id` int(11) unsigned DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `min_lat` double NOT NULL,
 `min_lon` double NOT NULL,
 `max_lat` double NOT NULL,
 `max_lon` double NOT NULL,
 `polygon` polygon NOT NULL /*!80003 SRID 4326 */,
 `area` float NOT NULL COMMENT 'vymera',
 `square` varchar(255) NOT NULL COMMENT 'ctverec',
 `code_small` varchar(255) NOT NULL COMMENT 'zkraceny kod',
 `valid_from` date DEFAULT NULL COMMENT 'platny od',
 `valid_to` date DEFAULT NULL COMMENT 'platny do',
 `culture` varchar(255) NOT NULL COMMENT 'nayev kultury',
 `date_added` datetime DEFAULT NULL,
 `date_modified` datetime DEFAULT NULL,
 `active` tinyint(4) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 KEY `land_block_owner_id` (`land_block_owner_id`),
 KEY `lat-lon` (`min_lat`,`max_lat`,`min_lon`,`max_lon`),
 KEY `company_id` (`company_id`),
 SPATIAL KEY `polygon` (`polygon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Suggested fix:
Query worked fine in 5.7

SELECT lb.* FROM `gps`.`land_block` lb WHERE st_within( ST_GeomFromText('POINT (50.181978 14.591256)', 4326) , lb.polygon )
Query took 0.0036 seconds

file is uploaded

I guess my coordinates are wrong, investigating

Hi Mr. Dvorak,

Beside checking your coordinates, couple of other comments.

If EXPLAIN shows that spatial key is used, then there is no problem.

Also, if you confirm that this behavior persists, we need all the data from that table.

i fixed the coordinates but the query was still slow
it shown in EXPLAIN but not used anyway
I uploaded the ibd file. Can you see it?

Hi Mr. Dvorak,

No, we do not see it.

Have you used "Files" tab ???

If not, how have you uploaded it ??? If you have used our SFTP site, then we need the name of the file !!!

Hi,

One more, very important detail.

Beside .ibd file, we also require the corresponding .cfg file. You simply have to follow the export / import procedure that is explained in our Reference Manual.

Many thanks in advance.

I did the export again, with cfg file.
Here's the file:
https://ulozto.net/!TuWkj83pz6QR/mysql-bug-data-96269-tar-gz

Also I overwrote the old file on ftp
mysql-bug-data-96269.tar.gz

it was not possible so mysql-bug-data-96269-2.tar.gz

Hi Mr. Dvorak,

We have managed to repeat the behaviour that you described.

However, we have discovered that there is already one verified bug, which makes this bug a duplicate.

The original bug is:

https://bugs.mysql.com/bug.php?id=96311

You can not see its contents, since it is private.

Thank you.

i can see it
it was submitted after my bug
will this be fixed? I can't use the database because of it
thanks

Hi,

I can only tell you that the bug is verified and forwarded to our Development team.

We do not have access to the scheduling of the bug fixing.

OK
can you increase the severity?
A query that took 100 ms now takes 500 s
So it's definitely critical
thanks

Done .....