使用大表DESC限制1加速查询

Question

MariaDB 10 (美辛)

查询执行得相当慢，大约需要90秒。

我尝试删除一些旧行，然后优化表。

SELECT ceil(rate * 8 / 1000000)
FROM db.Octets
WHERE id = 5344
order by datetime DESC
LIMIT 1;

查询需要很长时间才能执行。

+------+-------------+----------------+-------+---------------+------------------+---------+------+------+-------------+
| id   | select_type | table          | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+------+-------------+----------------+-------+---------------+------------------+---------+------+------+-------------+
|    1 | SIMPLE      | Octets         | index | NULL          | Octets_1_idx     | 8       | NULL |    1 | Using where |
+------+-------------+----------------+-------+---------------+------------------+---------+------+------+-------------+

Answer 1

您可以尝试添加一个复合冗余索引。

create index  idx2 on Octets ( id , datetime, rate)