Docs

Indexes

B-tree for ranges, hash for equality, unique and sparse when you ask. The pages stay the source of truth.

An index speeds a predicate. It is rebuilt from the document pages when the process starts, and maintained on insert, update, and delete. Index entries store the document id. An update may move the document to another page, and the id still finds it.

The sample load creates five indexes on emp. You can create the same ones yourself.

Create and drop

db.emp.createIndex({ sal: 1 }, { name: "sal_1" })
db.emp.createIndex({ job: "hashed" }, { name: "job_hashed" })
db.emp.createIndex({ ename: 1 }, { name: "ename_1", unique: true })
db.emp.createIndex({ deptno: 1, sal: 1 }, { name: "deptno_1_sal_1" })
db.emp.createIndex({ comm: 1 }, { name: "comm_1", sparse: true })
db.emp.getIndexes()
db.emp.dropIndex("sal_1")

dropIndexes with "*" drops every secondary index. The _id_ index stays. A unique violation while building rolls the spec back and writes nothing to the log.

A text index is separate. createIndexes with "text" is rejected. Word search uses the text index described on Text search, including kova.inverted on a view.

Which kind

Spec Use it for

{ field: 1 } or a compound key Equality on a prefix, then one range ($gt, $gte, $lt, $lte) on the next field. Keys are stored ascending. Declaring -1 does not change which documents match.

{ field: "hashed" } One field, exact match only.

unique: true A second document with the same key is rejected before the log append. The error contains duplicate.

sparse: true A document missing any indexed field is left out of that index. A normal index stores a missing field as null.

Spec	Use it for
`{ field: 1 }` or a compound key	Equality on a prefix, then one range (`$gt`, `$gte`, `$lt`, `$lte`) on the next field. Keys are stored ascending. Declaring `-1` does not change which documents match.
`{ field: "hashed" }`	One field, exact match only.
`unique: true`	A second document with the same key is rejected before the log append. The error contains `duplicate`.
`sparse: true`	A document missing any indexed field is left out of that index. A normal index stores a missing field as null.

Numbers compare as ordered floating-point values, so 10 and 10.0 share a key. Array fields are not indexed element by element. Index the scalar you filter on.

What the planner picks

Equality on _id alone is IDHACK.
A predicate a secondary index covers is IXSCAN. The name comes back as indexName.
Anything else is COLLSCAN.

After the index returns ids, Kova reads those documents from the pages and applies the rest of the filter. An empty id list is an empty answer. find, count, and a leading $match use this path. A read inside an open transaction stays on that transaction's snapshot and does not switch to the index mid-flight.

db.emp.find({ sal: { $gt: 2000 } }).explain()

A SQL WHERE that is one comparison, such as sal > 1000 or job = 'CLERK', is planned the same way. DataFusion checks the predicate again. The SQL reply includes the stage that won.

List them from SQL

A SQL client can ask:

SHOW INDEXES FROM emp

SHOW KEYS and SHOW INDEX are the same question. The rows are the indexes on that collection.