MySQL Forums
Forum List  »  GIS

Spatial Query doesn't use spatial index..
Posted by: jb Caraus
Date: October 24, 2006 03:29AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Spatial Query doesn't use spatial index..
6186
October 24, 2006 03:29AM


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.