Re: GEO Search with large table is very slow

[Sean Gonsman]

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.