存储 unix epoch 的 INT 字段上的 MySQL 索引有多有效? [英] How effective is a MySQL index on an INT field storing unix epoch?

问题描述

很多关于设计强索引的信息都建议不要在数据重复极少的字段上使用它们.但是，由于基于时间的条件是我们应用程序的重要组成部分，因此人们建议在时间戳字段上使用索引以提高查询速度.

A lot of the information on designing strong indices recommends not using them on fields with extremely little data repetition. However, since time-based conditions are a big part of our application people have recommended using an index on the timestamp field to improve query speed.

为存储 unix epoch 的 INT 字段添加索引是否值得资源成本?除了在同一秒存储多条记录之外，时间戳基本上永远不会相同.

Is it worth the resource cost to add an index for an INT field storing the unix epoch? The timestamps are essentially never the same, aside from when multiple records are stored at the same second.

推荐答案

如果您的查询主要是关于"时间戳(即，涉及该字段的 where 子句显着限制了数据部分为被扫描)，或者您按时间戳排序大型结果集，那么我会说该字段上的索引将非常有用.

If your queries are primarily "about" timestamp (that is, where clauses involving that field significantly limit the portion of the data to be scanned), or you're ordering large-ish result sets by timestamp, then I'd say that an index on that field would be extremely useful.

索引没有什么神奇之处.就像您自己一样，如果按时间戳排序的时间戳和行号"(一种思想抽象，不打算与任何真实事物相关联)列表将有助于更快地回答问题并减少查看数据.

There's nothing magical about indexes. Just as yourself if a list of timestamps and "row numbers" (a thought-abstraction not intended to correlate with anything real), sorted by the timestamp, would be useful in answering questions more quickly and by looking at less of the data.

这篇关于存储 unix epoch 的 INT 字段上的 MySQL 索引有多有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！