主页 / dba / 问题 / 52529
Accepted
kellogs
Asked: 2013-10-31 11:35:51 +0800 CST

不考虑索引的查询

  • 772
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;

回来了

+----+-------------+------------+--------+---------------------+---------+---------+----------------+--------+----------+----------------+
| 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 |                |
+----+-------------+------------+--------+---------------------+---------+---------+----------------+--------+----------+----------------+

按此顺序在 (lat, lon, time) 列上有一个键。有没有办法说服 MySQL 将它用于此查询？

index mysql-5.1

1 个回答

  1. Best Answer

    首先是 SQL_CALC_FOUND_ROWS 是邪恶的。只是不要使用它，因为索引不能总是与它一起使用。

    至于问题的第二部分，mysql 不能对两个有序查询使用索引。我在这里得到了其中的 3 个——纬度和经度范围扫描和时间排序。

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

    • 0

相关问题