MySQL Forums
Forum List  »  GIS

Why my query took so long
Posted by: kittys seattle
Date: December 09, 2009 11:58AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Why my query took so long
3721
December 09, 2009 11:58AM
2144
December 22, 2009 10:21AM


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.