0
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?

0

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.