CODEWITHSUNDEEP
mongodbmongodb-querymongodb-indexes
vostock
vostock

Reputation: 249

How can i optimize my schema to remove useless default index on "_id"

This is my document structure :

db.like
{
  _id: objectid, /* not null */ 
  id_from: int64, /* not null */
  id_to: int64, /* not null */
  date: datetime, /* default NOW not null */
}

db.like.createIndex( {"id_to": 1, "id_from": 1}, {unique: true} );  
db.like.createIndex( {"id_to": 1, "date": -1}, {unique: false} );

I load document only in one of these ways:

db.like.find({$and: [{id_to:xxx}, {id_from:yyyy}]})

or

db.like.find({id_to:xxx}).sort({date:-1});

and later i shard the collection like this :

sh.shardCollection( "like", {"id_to": 1, "id_from": 1}, unique: true );

As you see i don't use at all the index on "_id". I m a little worry to have an index on "_id" that seam to be useless. Is their a way to optimize my schema or better to leave it like this ?

NOTE: the solution must work with sharding, so the solution given by clcto seam to be bad for this! it's solution was to declare _id as a document like : 

{
   _id : { 
      to : int64,
      from : int64
   },
   date : datetime
}

but i m quite sure that with such declaration query like

db.like.find({id_to:xxx}).sort({date:-1});

will be done on all shards

Upvotes: 0

Views: 93

Answers (1)

clcto
clcto

Reputation: 9648

Mongo is designed so that each document needs to have a unique id, which is ensured by the unique index on _id. AFAIK, you cannot remove it, but if you are able to change the schema, you can set up your document so that _id is a document containing id_to and id_from, since from the code provided is guaranteed to be unique:

{
   _id : { 
      to : int64,
      from : int64
   },
   date : datetime
}

For the indexes, since the id index is already created, so you don't need that one. You can index into the _id document for the second:

db.like.createIndex( {"_id.to": 1, "date": -1}, {unique: false} );

Then your queries would be:

db.like.find({ _id : { to: xxx, from: yyyy } });
db.like.find({ _id.to: xxx }).sort({date:-1});

Note: MongoDB requires the _id to be immutable, so if you need to be able to update the original fields id_to and id_from, you cannot use this method.

Upvotes: 1

Related Questions