Basically I have a site which needs to do a bounding box search and order the results by price, and provide a limit, so I have a query that is like …
SELECT * FROM table WHERE longitude BETWEEN -3 AND -1 AND latitude BETWEEN 50 AND 52 ORDER BY price LIMIT 20,30
The table has several 100’000 rows in it, but searches are very slow. I setup an index called location that holds BTREE indexes for longitude and latitude. and in index for price. I also tried combining location so it longitude, latitude, and price combined in it and it is still very slow. Obviously my main application has a tad more to the query (small join, and more in the WHERE clause but I know these are not the slow point in my query)
I know for a highly populated area that my bounding ‘box’ area will sometimes contain around 40000 results, which need to be sorted for before LIMIT can operate. However because it uses an index for the bounding box created by the lat/longs, it doesn’t seem to be able to use any indexes on the price, so a filesort is needed on 40000 odd rows at times, even if the location index has latitude, longitude and price inside it.
Is there anything I can do here to allow MySQL to make better use of indexes. I know FORCE INDEX on price would help a little on slow queries when the number of results with in the bounds is high, however it’s still too slow for my needs (web app), and I do know in these cases that if I switched to PostgreSQL the query would be a lot quicker for this given situation as it is able to combine several indexes when multiple criertia are needed. However I’d rather stick with MySQL because all my other queries are very simple and thus would be faster on MySQL as PostgreSQL has a little more overhead with processing queries.
Any ideas anyone. I would be very grateful!