Mysql GIS: Why my query took so long

[xshu]

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

[MetalHippy]

Hi

I'm not a MySql expert by any means (MSSQL server background through work) but when we have a similar type of issues it is because the table has not been indexed. Indexing the table significantly increases performance. I found this article relating to indexinx/optimising mysql table/queries..

http://www.databasejournal.com/featu...nd-Indexes.htm

Hope this helps in some way

Mike

[xshu]

Thanks for the reply, Mike.

The table already has been indexed.

SPATIAL INDEX point1_index (point1) USING RTREE,
SPATIAL INDEX point2_index (point2) USING RTREE

This is why I am confused why the query still took that long.

Beth

[MetalHippy]

how much activity (inserts/deletions etc) is there on the records in the table? We have some tables in our system that get a few thousand records added to them per day and if we dont re-build the indexes every morning then performance goes out the window. That may be the issue.

Mike

[xshu]

Currently we are just simply testing what technologies are suitable to query large data set. So all the data was populated right after the database was created. After that we do not have any update activity.

Beth

how much activity (inserts/deletions etc) is there on the records in the table? We have some tables in our system that get a few thousand records added to them per day and if we dont re-build the indexes every morning then performance goes out the window. That may be the issue.

Mike

[MetalHippy]

try changing the select count(*) to select count(column1)

when you use * it looks at the whole table whereas if you specify a single column the query doesn't look at the other columns. That may possibly speed it up a little (hopfully )

[xshu]

It is still slow . But thank you very much for your help, Mike.

Beth

try changing the select count(*) to select count(column1)

when you use * it looks at the whole table whereas if you specify a single column the query doesn't look at the other columns. That may possibly speed it up a little (hopfully )

[r937]

try changing the select count(*) to select count(column1)

actually, it's the other way around

yes, using the dreaded, evil "select star" is to be avoided, but this rule is for the SELECT clause itself, and ~not~ for the COUNT function

in fact, COUNT(*) is the fastest you can count, while COUNT(somecolumn) is always slower because it has to examine each row's value for somecolumn, since COUNT ignores NULLs

[MetalHippy]

actually, it's the other way around

yes, using the dreaded, evil "select star" is to be avoided, but this rule is for the SELECT clause itself, and ~not~ for the COUNT function

in fact, COUNT(*) is the fastest you can count, while COUNT(somecolumn) is always slower because it has to examine each row's value for somecolumn, since COUNT ignores NULLs

Cheers - I never knew that