MySQL Forums :: Performance :: GEO Search with large table is very slow
GEO Search with large table is very slow
Posted by: Sean Gonsman ()
Date: March 11, 2009 01:35PM
OK, so I have been trying to perfect a radius search for a large table for several years now. I understand how to do it, but when I do it on a large table the performance is horrible. The table I am using has about 1.2 million records (548MB). My Query is quite simple - no joins - just a radius search and other conditions that must be met. I have worked on the indexes as much as I can, but I can't seem to make any headway. Any insight into this would be amazing!
Here is my query:
Here is my query:
SELECT prop.sold_price, prop.list_price, prop.listing_time, prop.listing_pending_time FROM properties_2 AS prop FORCE INDEX(CMASearch) WHERE 1 AND prop.beds IN (3,4) AND prop.baths_full IN (2,3) AND prop.xref_properties_class_id = '1' AND prop.latitude >= 34.01559911 AND prop.latitude <= 34.03007089 AND prop.longitude >= -84.4255729623 AND prop.longitude <= -84.4067050377 AND prop.xref_status_id = '3' AND prop.listing_sold_time >= '2008-03-11' AND prop.listing_sold_time <= '2009-03-11'It takes about 30 seconds to process. here is the EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE prop range CMASearch CMASearch 29 NULL 3992 Using whereAnd here are the columns in the index (in order):
beds baths_full xref_properties_class_id xref_status_id latitude
|GEO Search with large table is very slow||7110||Sean Gonsman||03/11/2009 01:35PM|
|Re: GEO Search with large table is very slow||2760||Rick James||03/11/2009 07:36PM|
|Re: GEO Search with large table is very slow||2794||Markus Graeser||03/13/2009 08:12AM|
|Re: GEO Search with large table is very slow||2571||Rick James||03/13/2009 07:53PM|
|Re: GEO Search with large table is very slow||2795||Sean Gonsman||03/18/2009 09:27AM|
|Re: GEO Search with large table is very slow||4056||Rick James||03/18/2009 09:02PM|
|Re: GEO Search with large table is very slow||2914||Sean Gonsman||04/04/2009 07:03AM|
|Re: GEO Search with large table is very slow||2801||Rick James||04/04/2009 12:51PM|
|Re: GEO Search with large table is very slow||2361||Sean Gonsman||04/06/2009 09:59AM|
|Re: GEO Search with large table is very slow||2334||Rick James||04/06/2009 07:38PM|
|Re: GEO Search with large table is very slow||2458||Jack Gwalt||03/15/2009 07:41PM|
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.