MySQL Forums
Forum List  »  GIS

Am I doing something wrong or I've hit a bug ?
Posted by: Gabriel Tataranu
Date: February 04, 2008 11:28AM

mysql> show create table p1t;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| p1t | CREATE TABLE `p1t` (
`ll` point NOT NULL,
SPATIAL KEY `p1_spac_idx` (`ll`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> set @poly=GeomFromText('POLYGON((222939 98856,225839 98856,225839 101756,222939 101756,222939 98856))');
Query OK, 0 rows affected (0.00 sec)

mysql> explain extended select count(*) from p1t where MBRContains(@poly,ll) limit 11;
+----+-------------+-------+-------+---------------+-------------+---------+------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | p1t | range | p1_spac_idx | p1_spac_idx | 32 | NULL | 15884164 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+-------------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select count(*) from p1t where MBRContains(@poly,ll) limit 11;
+----------+
| count(*) |
+----------+
| 228052 |
+----------+
1 row in set (0.64 sec)


Notice the difference between the optimizer estimate and the real number ? The p1t table has about 18M rows.

One more thing: there is "limit 11" at the end of the statement. It seem useless but look what's the execution plan without it.

mysql> explain extended select count(*) from p1t where MBRContains(@poly,ll);
+----+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | p1t | ALL | p1_spac_idx | NULL | NULL | NULL | 18152921 | 87.50 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


Weird, huh ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Am I doing something wrong or I've hit a bug ?
3613
February 04, 2008 11:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.