Why my query took so long

[kittys seattle]

Hi there:

I ran a query on a table that has two columns of type POINT with spatial indexing being applied (using R+ tree). The total records in the table is 480,847,800.

This is how the table is created.
CREATE TABLE table1 (
column1 varchar(11) NOT NULL default '1-1-174-418',
startPoint POINT NOT NULL,
endPoint POINT NOT NULL,
SPATIAL INDEX point1_index (point1) USING RTREE,
SPATIAL INDEX point2_index (point2) USING RTREE
) TYPE=MyISAM;

Below is the query I executed. The total time for the query was about 23 mins which is definitely not acceptable. I am wondering if the query has something wrong or is there any improvement I can do?

select count(*) from table1 where Contains(LineStringFromWKB(AsBinary(startPoint), AsBinary(endPoint)),LineStringFromText('LINESTRING(5 0, 38 0)'));

Thanks!
Beth



Edited 2 time(s). Last edit at 12/09/2009 04:25PM by kittys seattle.