Spatial Query doesn't use spatial index..

[jb Caraus]

Hello,

VERSION: mysql 5.0.25

I've created a table with a POINT type column (mem_gp), and i've added a spatial index (sp_index).

CREATE TABLE `geoInfo` (
`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=295575 DEFAULT CHARSET=latin1 AUTO_INCREMENT=295575

Problem 1:

- When I try to optimize the table, i get an "Can't read key block from filepos: 723761592142120..."

Problem 2:

If i perform a query like this:

"SELECT * FROM geoInfo 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"

The EXPLAIN is:

1 SIMPLE members ALL sp_index NULL NULL NULL 205242 Using where

If i decide to force the index, the explain is:

1 SIMPLE members range sp_index sp_index 32 NULL 68417 Using where

I don't get it why mysql needs 68417 rows to find the location, does Mysql use correctly the index ? (it doesn't want to use it without a force index, are there any reasons?)

Thanks a lot for your answer, i'm getting mad. Please forgive my poor english, it's not my native languages.



Edited 1 time(s). Last edit at 10/24/2006 03:34AM by jb Caraus.