Re: Spatial Query doesn't use spatial index..

[Alexander Barkov]

I could not reproduce the problems you describe:


mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`mem_userid` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
`mem_gp` point NOT NULL,
`mem_country` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`mem_userid`),
SPATIAL KEY `sp_index` (`mem_gp`(32))
) ENGINE=MyISAM AUTO_INCREMENT=295582 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> select AsText(mem_gp) from t1;
+----------------------+
| AsText(mem_gp) |
+----------------------+
| POINT(1 1) |
| POINT(2 1) |
| POINT(2 3) |
| POINT(2 4) |
| POINT(2 5) |
| POINT(2 6) |
| POINT(46.262 -1.177) |
+----------------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE Contains( GeomFromText( 'POLYGON( ( 46.262 -1.177,46.212 -1.09,46.112 -1.09,46.062 -1.177,46.112 -1.263,46.212 -1.263,46.262 -1.177 ) )' ) ,mem_gp ) LIMIT 30;
+------------+---------------------------+-------------+
| mem_userid | mem_gp | mem_country |
+------------+---------------------------+-------------+
| 295581 | | 0 |
+------------+---------------------------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM t1 WHERE Contains( GeomFromText( 'POLYGON( ( 46.262 -1.177,46.212 -1.09,46.112 -1.09,46.062 -1.177,46.112 -1.263,46.212 -1.263,46.262 -1.177 ) )' ) ,mem_gp ) LIMIT 30;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | sp_index | sp_index | 32 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> optimize table t1;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status | OK |
+---------+----------+----------+----------+
1 row in set (0.00 sec)


It seems your table got corrupted for some reasons. Try to repair it.