1

If I understand correctly, secondary index leaf nodes in MySQL InnoDB engine hold the table primary key value (at least for a unique index).

Thus, looking up a value in the secondary index results in two BTREE lookups: one for the secondary index and another one for the clustered index (clustered on the primary key). This also means that the primary key size affects the size of all secondary indexes.

Is this how MongoDB WiredTiger secondary indexes work too? Or do MongoDB secondary indexes store a reference to the physical block where the document resides? (I believe this is how Postgres handles indexes)

2

I understand correctly, secondary index leaf nodes in MySQL InnoDB engine hold the table primary key value (at least for a unique index).

InnoDB tables have a clustered index which determines where data for rows is stored. The clustered index for an InnoDB table is either the PRIMARY KEY (if set), the first unique index with all key columns NOT NULL, or hidden index containing synthetic row ID values. Secondary index entries will include a reference to the clustered index for the record location.

Is this how MongoDB WiredTiger secondary indexes work too?* Or do MongoDB secondary indexes store a reference to the physical block where the document resides?

MongoDB (as at 4.0) does not support clustered indexes. The WiredTiger storage engine uses an approach similar to the third option described for InnoDB: an internal unique RecordID which is a 64-bit integer.

The WiredTiger storage engine currently uses a separate file for each collection and index. Collection data (collection-*.wt) is indexed using the internal RecordID; indexes (index-*.wt) map keys to RecordIDs in the associated collection data. These implementation details aren't relevant for the MongoDB API, which provides a consistent end user interface for indexing, querying, and manipulating data. Storage engines have ownership over how data is represented on disk and in memory. The approach is analogous to what happens under the hood in other database engines like InnoDB (where end users are interacting via the MySQL API).

The size of your primary key (which would be the _id field in MongoDB) currently has no impact on the size of secondary indexes in WiredTiger.

Incidentally, if you happen to be using the older (and deprecated) MMAPv1 MongoDB storage engine it does store references to the disk location (file and offset) where data resides. A significant downside of this approach is that document moves in storage (due to document growth) also require updating all secondary indexes. WiredTiger only needs to update secondary indexes when associated field values are changed.

  • Does that mean that primary key ('_id') searches and secondary indexes searches are identical in terms of performance? How does WiredTiger find the document given the internal 64-bit unique id? Is there some public information (besides the source code) that describes the internals of MongoDB? Thanks! – nimrodm May 30 at 11:00
  • 1
    It means that primary and secondary indexes work similarly. Performance is a broader topic and depends on factors like the index type, queries, and value distribution. The primary index currently does not have any specific advantages such as clustering, but ObjectIds do work well with the default index prefix compression. Outside of source code I don't think there are many detailed tours of storage engine internals as these are at a much lower level than the end user MongoDB API. – Stennie May 30 at 14:10
  • 1
    For more insight into WiredTiger internals I'd start with A Technical Introduction to WiredTiger (MongoDB 3.0, but still applicable) and then the Path to Transactions series (MongoDB 4.0). The MongoDB Engineering Journal also has some great articles. If you are able to attend a MongoDB conference, these include Ask The Expert opportunities for 1-on-1 discussion with engineers and consultants. – Stennie May 30 at 14:10
  • 1
    @nimrodm I updated with a description of WiredTiger data files to address your comment: "How does WiredTiger find the document?". – Stennie May 31 at 2:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.