query not taking index into account EXPLAIN EXTENDED SELECT SQL_CALC_FOUND_ROWS SQL_NO_CACHE g.*, u.user_id FROM geotable g JOIN userstable u ON g.uid = u.uid
                   -- corners selection:
WHERE (lat BETWEEN ? and ? AND lon BETWEEN ? and ?) ORDER BY time ASC;


comes back with

+----+-------------+------------+--------+---------------------+---------+---------+----------------+--------+----------+----------------+
| id | select_type | table      | type   | possible_keys       | key     | key_len | ref            | rows   | filtered | Extra          |
+----+-------------+------------+--------+---------------------+---------+---------+----------------+--------+----------+----------------+
|  1 | PRIMARY     | g          | ALL    | uk_uid,lat_lon_time | NULL    | NULL    | NULL           | 199917 |     2.15 | Using filesort |
|  9 | DERIVED     | u          | eq_ref | PRIMARY             | PRIMARY | 4       | tigasedb.g.uid |      1 |   100.00 |                |
+----+-------------+------------+--------+---------------------+---------+---------+----------------+--------+----------+----------------+


There's a key on (lat, lon, time) columns in this order. Is there a way to convince MySQL to use it for this query?
First thing is that SQL_CALC_FOUND_ROWS is evil. Just do not use it as indexes can't always be used together with it.

As for the scond part of the issue, mysql can not use an index for two ordered queries. And I got 3 of them here - lat and lon range scans and a time sorting. 

See https://stackoverflow.com/questions/4083632/can-mysql-use-index-in-a-range-query-with-order-by