| Bug #90942 | MbrCovers() function never use spatial indexes | ||
|---|---|---|---|
| Submitted: | 19 May 2018 15:22 | Modified: | 24 May 2018 15:58 |
| Reporter: | Yannick W | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S5 (Performance) |
| Version: | 5.7.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | gis, INDEX, mbrcovers, spatial | ||
[20 May 2018 18:09]
MySQL Verification Team
Probably a Documentation or Server Error bug, since 8.0 shows the warning why that behavior:
C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 Source distribution BUILD: 2018-MAY-16
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 8.0 > CREATE DATABASE d1;
Query OK, 1 row affected (0.07 sec)
mysql 8.0 > USE d1
Database changed
mysql 8.0 > CREATE TABLE segments (
-> `data` LINESTRING NOT NULL,
-> SPATIAL INDEX `data_idx` (`data`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
mysql 8.0 > SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 3719
Message: 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
*************************** 2. row ***************************
Level: Warning
Code: 3674
Message: The spatial index on column 'data' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
2 rows in set (0.00 sec)
mysql 8.0 >
[20 May 2018 18:13]
MySQL Verification Team
See https://dev.mysql.com/worklog/task/?id=10439.
[22 May 2018 12:57]
MySQL Verification Team
Hi, Please, respond to the findings of my colleague Godofredo Miguel Solorzano. Simply, if you do not supply GIS info, there will be nothing to be indexed.
[22 May 2018 20:45]
Yannick W
Sorry for the late response. However I'm not sure to understand. Do I need to define the SRID attribute in the column definition? If so, how can I do this?
[23 May 2018 11:53]
MySQL Verification Team
HI, There are several issues here. First of all, SRID could be added, but it is not obligatory. More important is that server recognises the input data as the one of the valid geometry types for the data type of your choice. Our Reference Manual contains all the info on the acceptable values for the particular GIS type. If you enter such a value, then index entry will be created, otherwise it will be NULL. Most important of all is that index will never be used for the table with small number of rows. Small depends of the table structure, but you might need 50, 100 or more rows so that index search becomes faster then row scanning. This is also explained in our Reference Manual.
[23 May 2018 14:36]
Yannick W
Thank you Sinisa.
But my issue is for MySQL 5.7.19 and I can't add the SRID attribute to the column definition.
-----
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 313025
Server version: 5.7.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE test.segments (
-> `data` GEOMETRY NOT NULL SRID 90,
-> SPATIAL INDEX `data_idx` (`data`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SRID 90,
SPATIAL INDEX `data_idx` (`data`)
) ENGINE=InnoDB DEFAULT CHARSET=utf' at line 2
-----
[23 May 2018 15:18]
MySQL Verification Team
Hi, Just skip adding SRID and concentrate on two other issues. Make sure that you are inserting GIS data , by looking at the warning. Also, don't forget that index (if data are entered correctly) can not be used with a small number of rows , as I explained.
[23 May 2018 18:03]
Yannick W
Hi,
I have re-created the table (without the SRID attribute), then added more than 10000 rows but I have still the same behavior, the index is not used.
-----
mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.02 sec)
mysql> USE d1;
Database changed
mysql>
mysql> CREATE TABLE segments (
-> `data` GEOMETRY NOT NULL,
-> SPATIAL INDEX `data_idx` (`data`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW WARNINGS;
Empty set (0.00 sec)
[multiple INSERT INTO...]
mysql> INSERT INTO segments (`data`) VALUES (ST_GeometryFromText('LineString(0 100000, 0 100010)'));
Query OK, 1 row affected (0.00 sec)
mysql> SHOW WARNINGS;
Empty set (0.00 sec)
mysql> EXPLAIN SELECT *
-> FROM segments
-> WHERE MBRCovers(data, ST_GeomFromText('Point(0 11)'));
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | segments | NULL | ALL | data_idx | NULL | NULL | NULL | 10018 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS \G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`segments`.`data` AS `data` from `test`.`segments` where mbrcovers(`test`.`segments`.`data`,<cache>(st_geometryfromtext('Point(0 11)')))
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> EXPLAIN SELECT *
-> FROM segments FORCE INDEX (data_idx)
-> WHERE MBRCovers(data, ST_GeomFromText('Point(0 11)'));
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | segments | NULL | ALL | data_idx | NULL | NULL | NULL | 10018 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS \G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`segments`.`data` AS `data` from `test`.`segments` FORCE INDEX (`data_idx`) where mbrcovers(`test`.`segments`.`data`,<cache>(st_geometryfromtext('Point(0 11)')))
1 row in set (0.00 sec)
ERROR:
No query specified
[24 May 2018 12:47]
MySQL Verification Team
Hi,
Seems like your query is wrong.
Try to do the other way around. Instead of:
WHERE MBRCovers(data, ST_GeomFromText('Point(0 11)'))
try:
WHERE MBRCovers(ST_GeomFromText('Point(0 11)'), data)
and let us know the results.
[24 May 2018 13:59]
Yannick W
Hi, Not sure, I've always done it this way. Please see the documentation: https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-mbr.html#function_mbrco... In the last example, I wanted to find all lines that contain the point (0 11). I have inserted a lot of lines like this: INSERT INTO segments (`data`) VALUES (ST_GeometryFromText('LineString(0 0, 0 10)')); INSERT INTO segments (`data`) VALUES (ST_GeometryFromText('LineString(0 10, 0 20)')); INSERT INTO segments (`data`) VALUES (ST_GeometryFromText('LineString(0 20, 0 30)')); [...] INSERT INTO segments (`data`) VALUES (ST_GeometryFromText('LineString(0 100000, 0 100010)')); The query: SELECT * FROM segments WHERE MBRCovers(data, ST_GeomFromText('Point(0 11)')); should return the rows with LineString(0 10, 0 20) The query: SELECT * FROM segments WHERE MBRCovers(data, ST_GeomFromText('Point(0 20)')); should return the rows with LineString(0 10, 0 20) and LineString(0 20, 0 30)
[24 May 2018 14:08]
MySQL Verification Team
Please read the chapter on using spatial indices in our Reference Manual. That shows the correct way of using it, if you want your index to work.
[24 May 2018 15:58]
Yannick W
Lol... and so that's what the support is for...
Description: In 5.7 The behavior of MbrContains() was changed and a new function MbrCovers() was implemented to allow the old behavior of MbrContains() that was in 5.6. However, when using queries with MbrCovers() spatial indexes are never used. How to repeat: CREATE TABLE segments ( `data` LINESTRING NOT NULL, SPATIAL INDEX `data_idx` (`data`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO segments (`data`) VALUES (ST_GeomFromText('LineString(0 0, 0 10)')); EXPLAIN SELECT * FROM segments FORCE INDEX (data_idx) WHERE MBRCovers(data, ST_GeomFromText(CONCAT('Point(0 5)'))); -------------------------------- Result: +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | segments | NULL | ALL | data_idx | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) -------------------------------- Expected result: +----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | segments | NULL | range | data_idx | data_idx | 34 | NULL | 1 | 100.00 | Using where | +----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ Suggested fix: FYI, this issue has been reported on Percona but not yet resolved: https://jira.percona.com/browse/PS-3684