15

I've tried to search through Mongo documentation, but can't really find any details on whether queries on unique indexes will be faster than queries on non-unique indexes (given the same data)

So I understand that a unique index will have high selectivity and good performance. But, given two fields whose concatenation is unique, would a non-unique compound index perform slower than a unique compound index?

I am assuming that unique indexes can slow down inserts as the uniqueness must be verified. But is the read performance improvement of a unique index, if any, really worth it?

Zaid Masud
  • 13,225
  • 9
  • 67
  • 88
  • Did you get a definitive answer to this? I am doing some bulk upserts (3,000,000+ operations) and using an index other than _id was incomparably slower. Many orders of magnitude. I was using a 2d (non-unique, but could be) geolocation index to base the updates on. I'm going to try again making the geolocation index unique and see if I get a performance improvement. – ephemer Mar 04 '16 at 00:41
  • I agree with you that it should make sense to use a different algorithm when making a unique index. However, they did not. I've also asked one of the solutions architects in mongo and he said there is no difference in performance for searching. – madarinho Sep 18 '22 at 09:19

2 Answers2

20

A quick grep of the source tree seems to indicate that unique indexes are only used on insert, so there shouldn't be any performance benefit or detriment between a query that returns one document, whether the index is unique or not.

MongoDB indexes are implemented as btrees, so it wouldn't make any logical sense for them to perform any differently whether the index is unique or not.

Chris Heald
  • 61,439
  • 10
  • 123
  • 137
  • 7
    Thanks, although the one difference I can think of when scanning a unique index btree is that you can stop scanning after the first match is found.. so in that case it might make "logical sense" to perform (slightly) faster? I also note that when you say "returns one document" you imply that limit (1) is specified in the query, in which case there would be no difference using this logic. – Zaid Masud Sep 10 '12 at 12:47
0

I did my own small research on that topic. I generated 500,000 records (randomly generated strings) in a collection, and tried a couple of queries with explain() statement. db.test.find() with no indexes

Then I ensured a unique index, and tried few other queries again: db.test.find() with unique index

As you can see, after adding index the time consumption decreased from ~276ms to 0ms! So it seems like even if the index is unique, it affects (in a positive way) the find queries.

ecdeveloper
  • 2,777
  • 1
  • 22
  • 16