Bug #73655 | ha_innobase::index_flags() reports bogus flags for spatial indexes | ||
---|---|---|---|
Submitted: | 20 Aug 2014 12:07 | Modified: | 21 Mar 2015 16:49 |
Reporter: | Marko Mäkelä | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.7.5 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[20 Aug 2014 12:07]
Marko Mäkelä
[18 Mar 2015 2:44]
Allen Lai
Posted by developer: If we remove the flag HA_READ_RANGE for spatial index, the query plan will be changed. Here's the example: CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POLYGON NOT NULL, spatial index idx3 (c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; INSERT INTO tab(c1,c2) VALUES(1, ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); INSERT INTO tab(c1,c2) VALUES(2, ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))')); SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); EXPLAIN SELECT c1,ST_Astext(c2) FROM tab WHERE MBRContains(tab.c2, @g1); ---------------------------------------------------------------------------------------- Before remove it: root@localhost [test] > EXPLAIN SELECT c1,ST_Astext(c2) FROM tab WHERE MBRContains(tab.c2, @g1); +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tab | NULL | range | idx3 | idx3 | 34 | NULL | 2 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) After remove it: root@localhost [test] > EXPLAIN SELECT c1,ST_Astext(c2) FROM tab WHERE MBRContains(tab.c2, @g1); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tab | NULL | ALL | idx3 | NULL | NULL | NULL | 2 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
[18 Mar 2015 9:10]
Norvald Ryeng
Posted by developer: This is an InnoDB issue, so reassigning it to the InnoDB category.
[20 Mar 2015 2:26]
Allen Lai
Posted by developer: As dicussed with Novald, we removed HA_READ_PREV and HA_DO_INDEX_COND_PUSHDOWN for spatial index.
[21 Mar 2015 16:49]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.7.8, 5.8.0 releases, and here's the changelog entry: The "ha_innobase::index_flags" function returned invalid flags for spatial indexes. Thank you for the bug report.