Choosing the right database index type

Question

I have a very simple Mongo database for a personal nodejs project. It's basically just records of registered users.

My most important field is an alpha-numeric string (let's call it user_id and assume it can't be only numeric) of about 15 to 20 characters.

Now the most important operation is checking if the user exists at or all not. I do this by querying db.collection.find("user_id": "testuser-123")

if no record returns, I save the user along with some other not so important data like first name, last and signup date.

Now I obviously want to make user_id an index. I read the Indexing Tutorials on the official MongoDB Manual.

First I tried setting a text index because I thought that would fit the alpha-numeric field. I also tried setting language:none. But it turned out that my query returned in ~12ms instead of 6ms without indexing.

Then I tried just setting an ordered index like {user_id: 1}, but I haven't seen any difference (is it only working for numeric values?).

Can anyone recommend me the best type of index for this case or quickest query to check if the user exists? Or maybe is MongoDB not the best match for this?

Answer 1

Some random thoughts first:

• A text index is used to help full text search. Given your description this is not what is needed here, as, if I understand it well, you need to use an exact match of the whole field.
• Without any index, MongoDB will use a linear search. Using big O notation, this is an O(n) operation. With an (ordered) index, the search is performed in O(log(n)). That means that an index will dramatically speed up queries when you will have many documents. But you will not necessary see any improvement if you have a small number of documents. In that case, O(n) can even be worst than O(log(n)). Some database management systems don't even bother using the index if the optimizer estimate that it will not provide enough benefits. I don't know if MongoDB does that, though.

Given your use case, I think the proper index is an unique index. This is an ordered index that would prevent insertion of two identical documents.

In your application, do not test before insert. In real application, this could lead to race condition when you have concurrent inserts. If you use an unique index, just try to insert -- and be prepared to gracefully handle an error caused by a duplicate key.