Bug #50834 | MBRIntersects uses SPATIAL index only for the first argument | ||
---|---|---|---|
Submitted: | 2 Feb 2010 14:03 | Modified: | 13 May 2015 8:46 |
Reporter: | Alex Bolenok | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 5.1.35-community, 5.1.44-bzr | OS: | Any |
Assigned to: | Assigned Account | CPU Architecture: | Any |
Tags: | gis, INDEX, spatial |
[2 Feb 2010 14:03]
Alex Bolenok
[2 Feb 2010 18:12]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 5.1.44 from bzr: ... mysql> EXPLAIN -> SELECT COUNT(*) -> FROM t_big b -> JOIN t_small s -> ON MBRIntersects(s.rg, b.rg); +----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+ | 1 | SIMPLE | s | ALL | sx_small_rg | NULL | NULL | NULL | 1000 | | | 1 | SIMPLE | b | ALL | sx_big_rg | NULL | NULL | NULL | 24000 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN -> SELECT COUNT(*) -> FROM t_big b -> JOIN t_small s -> ON MBRIntersects(b.rg, s.rg); +----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------------------------+ | 1 | SIMPLE | s | ALL | sx_small_rg | NULL | NULL | NULL | 1000 | | | 1 | SIMPLE | b | ALL | sx_big_rg | NULL | NULL | NULL | 24000 | Range checked for each record (index map: 0x2) | +----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT COUNT(*) -> FROM t_big b -> JOIN t_small s -> ON MBRIntersects(s.rg, b.rg); +----------+ | COUNT(*) | +----------+ | 24694 | +----------+ 1 row in set (14.76 sec) mysql> SELECT COUNT(*) FROM t_big b JOIN t_small s ON MBRIntersects(s.rg, b.rg); +----------+ | COUNT(*) | +----------+ | 24694 | +----------+ 1 row in set (16.10 sec) mysql> SELECT COUNT(*) -> FROM t_big b -> JOIN t_small s -> ON MBRIntersects(b.rg, s.rg); +----------+ | COUNT(*) | +----------+ | 24694 | +----------+ 1 row in set (0.70 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 5.1.44-debug | +--------------+ 1 row in set (0.00 sec)
[13 May 2015 8:46]
Norvald Ryeng
Posted by developer: In 5.7, the optimizer chooses the same plan and is able to use the spatial index for both queries.