MySQL Forums
Forum List  »  Performance

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
7190
March 11, 2009 01:35PM


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.