Bug #76384 | Spatial index not used when checking return values explicitly | ||
---|---|---|---|
Submitted: | 19 Mar 2015 8:41 | Modified: | 20 Mar 2015 13:39 |
Reporter: | Johannes Staffans | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: GIS | Severity: | S2 (Serious) |
Version: | 5.6.19, 5.6.25, 5.7.8 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | INDEX, spatial, spatial index |
[19 Mar 2015 8:41]
Johannes Staffans
[20 Mar 2015 13:39]
MySQL Verification Team
Hello Johannes Staffans, Thank you for the report. Observed similar behavior with 5.6.25 and 5.7.8 builds. Thanks, Umesh
[20 Mar 2015 13:41]
MySQL Verification Team
// 5.7.8 root@localhost [(none)] > create database test; Query OK, 1 row affected (0.00 sec) root@localhost [(none)] > use test; Database changed root@localhost [test] > drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [test] > create table t1(a geometry not null, b geometry not null, -> spatial index(a),spatial index(b),key(a(25)),key(b(25)), -> unique key(a(25),b(25)),key(b(25),a(25)))engine=myisam; Query OK, 0 rows affected (0.01 sec) root@localhost [test] > drop procedure if exists p1; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [test] > root@localhost [test] > delimiter $ root@localhost [test] > root@localhost [test] > create procedure p1() -> begin -> declare iii bigint unsigned default 0; -> declare continue handler for sqlexception begin end; -> repeat -> insert into t1 set a=point(floor(rand()*iii),floor(rand()*iii)), b=point(floor(rand()*iii),floor(rand()*iii)); -> set iii:=iii+1; -> until iii>=5000 end repeat; -> end $ Query OK, 0 rows affected (0.00 sec) root@localhost [test] > root@localhost [test] > delimiter ; root@localhost [test] > root@localhost [test] > call p1(); Query OK, 1 row affected (0.35 sec) root@localhost [test] > explain select count(*) from t1 where MBRcontains(`a`, point(505, 2648))=1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4999 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) root@localhost [test] > explain select count(*) from t1 where MBRcontains(`a`, point(505, 2648))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: a_3,a,a_2 key: a key_len: 34 ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) root@localhost [test] > explain extended select count(*) from t1 where MBRcontains(`a`, point(505, 2648))=1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4999 filtered: 100.00 Extra: Using where 1 row in set, 2 warnings (0.00 sec) root@localhost [test] > show warnings; +---------+------+---------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. | | Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (mbrcontains(`test`.`t1`.`a`,<cache>(point(505,2648))) = 1) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) root@localhost [test] > explain extended select count(*) from t1 where MBRcontains(`a`, point(505, 2648))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: a_3,a,a_2 key: a key_len: 34 ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 2 warnings (0.00 sec) root@localhost [test] > show warnings; +---------+------+---------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------+ | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. | | Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where mbrcontains(`test`.`t1`.`a`,<cache>(point(505,2648))) | +---------+------+---------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)