CODEWITHSUNDEEP
javascriptdatabasemongodbindexing
Jat90
Jat90

Reputation: 663

MongoDB Indexes: String vs Int

New to Database management. I have a books database and I want to create an index for the ISBN which is 13 characters in length. Example: '9781509825889'

Currently I'm using a string index but I've read conflicting points on whether to store as an int or a string for indexing optimization.

It looks like I have Int32 or Int64 options and I think the above ISBN is too big for Int32 but is it worth switching to an Int64 for query optimization or should I just stick with string? Is there any significant performance gain?

Thanks

Upvotes: 6

Views: 6125

Answers (2)

prasad_
prasad_

Reputation: 14287

You have an option to create a string or a long (Int64). Int32 cannot accommodate the size.

The string data storage and index storage size will be slightly larger. For example, I created one million documents with number and string data types of isbn numbers (in two different collections). The example document looked like this: { "_id" : ObjectId("5dc8d8fef25cf42fe848076d"), "isbn" : 1000000999999 }. The db.collection.stats() shows the sizes information:

"size" : 36000000,
"count" : 1000000,
"avgObjSize" : 36,
"storageSize" : 12169216,
"indexSizes" : {
        "_id_" : 9920512,
        "isbn_1" : 10887168
},

"size" : 46000000,
"count" : 1000000,
"avgObjSize" : 46,
"storageSize" : 15015936,
"indexSizes" : {
        "_id_" : 9916416,
        "isbn_1" : 11018240
},

Also, noted the querying on isbn is equally fast, in general, irrespective of the datatype.

ISBNs are displayed as a structured number, e.g., 978-3-16-148410-0. In case you are likely to use it in that format in the application, it can be stored as a structured string.

Upvotes: 6

krishna Prasad
krishna Prasad

Reputation: 3812

MongoDb indexes does not depends on the data type of the fields, so it does not matters you put it into String or Int32 or Int64.

All indexes are hashed that's why it does not depends on the data type you used.

References:

  1. Does field type matter in a MongoDB index?

Upvotes: 7

Related Questions