How effective is a MySQL index on an INT field storing unix epoch?

Question

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.

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.

Answer 1

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.