How to pick indexes is the same for MongoDB as mySQL

I recently went to MongoDB Days, a conference about everything MongoDB in SF. Starting my career as a Systems Programmer then Web Developer, MySQL DB[Admin|Architect], to Software|System Architecture I like to keep an open mind about new technology and trends. When you work with a lot of different languages, and technology you find out that it’s basically the same Science from about 40 years ago.

An index in MongoDB is like an index in mySQL since a Btree is a Btree regardless of what application uses it. Just like with mySQL the best performance improvement for an application using MongoDB as a datastore is adding the correct indexes.


To create an index in MongoDB:

db.<tableName>.ensureIndex({ col#1:1, col#2:-1, col#3:1 }); // note 1 means ASC -1 means DESC

MongoDB follows the same left-most-prefix rule meaning

col#1, col#2, col#3 is an index
col#1, col#2 is an index
col#1 is an index

col#2, col#3 IS NOT AN INDEX

So, just like with mySQL for ONE compound index you get a total of THREE indexes if you follow the left-most-prefix rule, the columns from left to right (in order) in a compound index is an index.

MongoDB also gets a performance boost by using Covering indexes just like mySQL. What is a Covering index? Instead of reading from the datapage (or document store for MongoDB) which exists on disk your reading the data from the index which should be in memory for the most part. A common practice is to follow the left-most-prefix pattern, then add the columns which you are returning at the end of the compound index. For instance

SELECT photoId from Photos WHERE userId=? AND dateCreate=? AND privacy=?

The index in mySQL I would make is

ALTER TABLE Photos ADD INDEX `userId-dateCreate-privacy-photoId` (userId,dateCreate,privacy,photoId)

Thus following the left-most-prefix of a compound index I have an index on

userId, dateCreate, privacy, photoId
userId, dateCreate, privacy
userId, dateCreate
userId

and a Covering Index satisfied by the query above.

For mongoDB its the same

db.photos.ensureIndex({ userId: 1, dateCreate: 1, privacy: 1, photoId: 1});


So, in conclusion, understand the Computer Science of a Btree, Hash, LinkedList and you will understand how indexes work across technology and find that essentially it's the same. More info on indexes for mySQL can be found here.

Also note:

Explain in mongoDB is your friend just like Explain in mySQL