One of the most useful lessons I ever learned about designing database schemas was the utter uselessness of indexing and searching on datetime fields. Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all.
It was a revelation to me when I decided to experiment with having a indexed date-ONLY field and using that to search instead. It improved performance by almost two orders of magnitude. In hindsight, this should have been totally obvious if I had stopped to think about how indexing works. But, as is stated in this article, we have to keep relearning the same lessons. Maybe I should write an article about how useless it is to index a datetime field...
EDIT: This was something I ran into about 10 years ago. It is possible there was something else going on at the time that I didn't know about that caused the issue I was seeing. This is an anecdote from my past self. I have not had to use this technique since then, and we were using an on-prem server. It's possible that the rest of the table was not designed well and index I was trying to use was already inefficient for the resources that we had at the time.
This a very strange, and incorrect, conclusion you have come to. It doesn't matter that datetime fields are unique, as most of the time you are not searching for a particular date time, but a range. I.e. "show me all of rows created between date X and Y". In that case, the ordering of the index makes it efficient to do that query.
Furthermore, often times your query will have an "ORDER BY dateTimeCol" value on it (or, more commonly, ORDER BY dateTimeCol DESC). If your index is created correctly, it means it can return rows with a quick index scan instead of needing to implement a sort as part of the execution plan.
This entirely depends on what kind of index is used. A sorted index, such as a B or B+ tree (used in many SQL databases), will allow for fast point/range lookups in a continuous value space. A typical inverted index or hash based index only allows point lookups of specific values in a discrete value space.
Are you sure it was actually using the index you expected? There are subtleties in index field order that can prevent the query optimizer from using an index that you might think it should be using.
One common misstep is having a table with columns like (id, user, date), with an index on (user, id) and on (date, id), then issuing a query like "... WHERE user = 1 and date > ...". There is no optimal index for that query, so the optimizer will have to guess which one is better, or try to intersect them. In this example, it might use only the (user, id) index, and scan the date for all records with that user. A better index for this query would be (user, date, id).
One of the more bizarre things we'd found in MySQL 5.something was that accidentally creating two identical indexes significantly slowed down queries that used it.
I wouldn't be surprised if you hit some sort of similar strange bug.
> Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all.
Do you mean exact equality is rarely what you want because most of the values are different?
Or are you talking about the negative effect on index size of having so many distinct values?
I think the latter point could be quite database-dependent, eg. BTree de-duplication support was only added in Postgres 13. However, you could shave off quite a bit just from the fact that storing a date requires less space in the index than a datetime.
Your result is surprising: I suspect your wide table with an index wasn't in cache, and your timestamp-only table was.
The whole point is that indexes (can) prevent full table scans, which are expensive. This is true even if the column(s) you're indexing have high cardinality: but it relies on performant table or index joins (which, if your rdbms is configured with sufficient memory, should be the case).
I have no idea how you came to this conclusion, but indices on datetime fields are completely required to bring down the seek time from O(n) to O(log(N)), plus the length of the range. Massive parts of the businesses I've worked at would be simply impossible without this.
The cardinality of the index has other ramifications that aren't generally important here.
It was a revelation to me when I decided to experiment with having a indexed date-ONLY field and using that to search instead. It improved performance by almost two orders of magnitude. In hindsight, this should have been totally obvious if I had stopped to think about how indexing works. But, as is stated in this article, we have to keep relearning the same lessons. Maybe I should write an article about how useless it is to index a datetime field...
EDIT: This was something I ran into about 10 years ago. It is possible there was something else going on at the time that I didn't know about that caused the issue I was seeing. This is an anecdote from my past self. I have not had to use this technique since then, and we were using an on-prem server. It's possible that the rest of the table was not designed well and index I was trying to use was already inefficient for the resources that we had at the time.