Currently I have a collection events with an attribute createdAt of type date (for example 2019/09/09 10:32:12.123). Because my users search a lot by date, I added an index on this attribute. There are probably hundred of thousands items in my collection and most of these events have a different createdAt date (which means a different index value).

My users are complaining that it takes too much time to retrieve some data, so I'm looking for a way to improve that.

As my users only search by date (never with time), I was wondering if creating another attribute 'createdAtDateOnly' (keep only the date and set the time to 00:00, for example: 2019/09/09 00:00:00) would be faster to search because many 'events' in my collection would have the same index.

  • Have you profiled? Are you sure the problem is the database and not something the application does in memory against a large number of records? I can't be 100% sure, but I suspect that dropping the time from the index wouldn't help. Indexes usually improve performance by allowing the database to more quickly determine what subset of records are actually needed to generate the final result, allowing it to quickly eliminate a lot of rows from consideration and avoid the associate disk reads and in memory computations against them. A B-tree index on timestamp already does that for a range query. – jpmc26 Aug 27 '19 at 3:04

Indexes perform better when they have unique values like customer ID, acc number etc. The more unique your indexed field is the less number of records gets picked improving the performance. You can build index on the date field, but would that be unique enough to filter the record you need?

  • A more precise statement would be, "Queries using indexes are faster when they filter on a unique value because this allows the query to consider fewer recordss, reducing disk reads and in memory operations." Whether that's "better" is a matter of whether the problem the query solves allows for it; if many recordss are needed for the result to be correct, then an index that matches many records might be the best you can do. (Inversely, if the query needs to consider almost all records, anyway, an index is probably useless.) The asker's situation does not allow for searching by a unique value. – jpmc26 Aug 27 '19 at 2:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.