MySQL Forums
Forum List  »  Performance

Re: GEO Search with large table is very slow
Posted by: Markus Graeser
Date: March 13, 2009 08:12AM

30 seconds for examining some 4000 rows is really slow.

This could just be a disk access issue when running the first query. However, have you tried to have one index for each search-relevant column instead of a single index for all of them? If not, give it a try and then test your queries again, heavily using EXPLAIN. In the end you can get rid of indices you don't need, or merge (some of) them to a multi-column index with the column order that gives the best performance.

Depending on your data, lat/lon may reduce the result set more effectively than the number of beds. And storing ids like '1' and '3' as strings (xref_status_id, xref_properties_class_id) looks like a bad choice that bloats your storage size and slows down search.

And you'd probably better get rid of FORCE INDEX() unless you are absolutely sure that you have to use it:

Options: ReplyQuote

Written By
Re: GEO Search with large table is very slow
March 13, 2009 08:12AM

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.