SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
-
Dec 9, 2009, 17:06 #1
- Join Date
- Dec 2009
- Posts
- 4
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Mysql GIS: Why my query took so long
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
-
Dec 9, 2009, 17:33 #2
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
MikeOne Life - Live It
-
Dec 9, 2009, 17:42 #3
- Join Date
- Dec 2009
- Posts
- 4
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Dec 9, 2009, 17:50 #4
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.
MikeOne Life - Live It
-
Dec 9, 2009, 18:02 #5
- Join Date
- Dec 2009
- Posts
- 4
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Dec 9, 2009, 18:10 #6
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)
One Life - Live It
-
Dec 9, 2009, 18:43 #7
- Join Date
- Dec 2009
- Posts
- 4
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Dec 9, 2009, 21:56 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Dec 10, 2009, 04:27 #9
Bookmarks