MySQL: Is it better to have an index and a small filesort or no index and no filesort?

Question

I have a large database (180k+ rows and growing fast) of location data and am plotting them on a google map. For a given view port, I just want to serve up a sample of 100 applicable points. The database is therefore queried by lat/lng, but if I put an index on these rows the problem is that the sample of 100 points will be either at the bottom or the top of the view port (depending on how the index is used). If no index is used, the points are pretty much randomly scattered across the view port, which is much more desirable. I can create the same effect on the indexed results by doing a filesort by a third, pretty much random field.

So, the issue seems to be, what is better: An unindexed query on 180k+ rows, or an indexed query which will look at something like 4k rows & do a filesort? Thanks!

Answer 1

Profiling your query with index and without it. I use profiler in dbForge Studio for MySQL.

Answer 2

You'll find many arguments against using "ORDER BY RAND()", although it may be useful in this situation if you do index the field and you find the profiling results to be acceptable:

mysql> select id from table where id > 10000 and id < 20000 order by rand() limit 0,10;
+-------+
| id    |
+-------+
| 18560 | 
| 18408 | 
| 14058 | 
| 19090 | 
| 11100 | 
| 18945 | 
| 12656 | 
| 16549 | 
| 19321 | 
| 12003 | 
+-------+
10 rows in set (0.04 sec)