SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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

  2. #2
    SitePoint Enthusiast MetalHippy's Avatar
    Join Date
    Nov 2008
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    One Life - Live It

  3. #3
    SitePoint Member
    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

  4. #4
    SitePoint Enthusiast MetalHippy's Avatar
    Join Date
    Nov 2008
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    One Life - Live It

  5. #5
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


    Quote Originally Posted by MetalHippy View Post
    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

  6. #6
    SitePoint Enthusiast MetalHippy's Avatar
    Join Date
    Nov 2008
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is still slow . But thank you very much for your help, Mike.

    Beth

    Quote Originally Posted by MetalHippy View Post
    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 )

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MetalHippy View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast MetalHippy's Avatar
    Join Date
    Nov 2008
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    One Life - Live It


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •