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
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)'));