在时间戳上创建索引以优化查询 [英] Creating an index on a timestamp to optimize query
问题描述
我查询了以下表格:
SELECT * FROM MyTable WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime]
我想优化这个查询,我正在考虑在时间戳上加上索引,但我不确定这是否会有所帮助。理想情况下,我想使时间戳成为聚簇索引,但MySQL不支持聚簇索引,主键除外。
I would like to optimize this query, and I am thinking about putting an index on timestamp, but am not sure if this would help. Ideally I would like to make timestamp a clustered index, but MySQL does not support clustered indexes, except for primary keys.
-
MyTable
有400万+行。 -
时间戳
实际上是类型> INT
。 - 插入一行后,永远不会更改。
- 任何给定的行数
时间戳
平均约为20,但可能高达200。 - 新插入的行有
时间戳
大于大多数现有行,但可能少于一些较近期的行。
MyTable
has 4 million+ rows.Timestamp
is actually of typeINT
.- Once a row has been inserted, it is never changed.
- The number of rows with any given
Timestamp
is on average about 20, but could be as high as 200. - Newly inserted rows have a
Timestamp
that is greater than most of the existing rows, but could be less than some of the more recent rows.
Timestamp
上的索引会帮助我优化此查询吗?
Would an index on Timestamp
help me to optimize this query?
推荐答案
毫无疑问。如果没有索引,您的查询必须查看表中的每一行。使用索引,只要找到正确的行,查询就会非常即时。您需要支付的价格是插件的轻微性能下降;但这确实很轻微。
No question about it. Without the index, your query has to look at every row in the table. With the index, the query will be pretty much instantaneous as far as locating the right rows goes. The price you'll pay is a slight performance decrease in inserts; but that really will be slight.
这篇关于在时间戳上创建索引以优化查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!