MySQL Forums :: Performance :: GEO Search with large table is very slow


Advanced Search

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:
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 where
And here are the columns in the index (in order):
beds
baths_full
xref_properties_class_id
xref_status_id
latitude 

Options: ReplyQuote


Subject Views Written By Posted
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.