MySQL Forums
Forum List  »  Performance

Re: GEO Search with large table is very slow
Posted by: Sean Gonsman
Date: April 06, 2009 09:59AM

Thanks again Rick. I am making a few changes to the indexes and I hope I have found a good solution.

After some research it appears that using IN() and (col1 = 1 OR col1 = 2) are both OK for indexes. I think the problem comes up when you don't use the same column for the entire OR group (col1 = 1 or col2 = 1).

I have redone the index to include: xref_properties_class_id, xref_status_id, beds, baths_full, latitude. I took out listing_sold_time because it had so many unique values that the index was not indexing all of the values in latitude (this goes back to the number of rows allowed in the index). it seems that latitude narrows down the search better than listing_sold_time.

I don't have root access to restart MySQL so I may have to try one of the other tricks to get MySQL to flush the memory.

We are using MYSQL 5.

It seems to be really tricky to get indexes to work properly when you have large tables with varying amounts of data in each column.

Options: ReplyQuote

Written By
Re: GEO Search with large table is very slow
April 06, 2009 09:59AM

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.