Indexes

This is the page that pays for Explain plans. Explain tells you the query read the whole collection; this is where you do something about it.

Open the index dialog from a collection's context menu.

#What the list shows

For every index: its keys, its options, its size on disk, and how many times the server chose it and since when it has been counting.

An index nobody uses costs writes and disk space for nothing, and from a driver you cannot easily check.

Reading usage statistics needs permissions the connected user may not have. When they are missing, the dialog names the reason — not permitted, not supported, or unavailable — rather than showing an empty table. An empty table implying zero usage is the worst possible failure for this feature; it is how you drop the index a nightly job depends on.

The index list itself comes from ordinary listing rather than from the statistics command, so you always see which indexes exist even when no statistic can be read. "No number" and "zero" are different values and the interface distinguishes them.

After you create or drop an index the statistics are read again: a new index has served nothing yet, and a dropped one costs nothing now.

#Creating an index

TableCore builds the createIndexes entry itself rather than using the driver's helper, which does not express every option. The command carries an explicit name — the same one the server would have generated if you do not supply your own — and that name is what makes cancelling possible.

Combinations the server would only reject after building the whole index are rejected before the command is sent:

• a TTL index needs exactly one ordered field,
• unique is not available for hashed or wildcard keys,
• sparse does not combine with partialFilterExpression,
• wildcardProjection requires a $** key,
• collation must name a locale.

partialFilterExpression, collation and wildcardProjection are kept as text until they are checked, so the dialog can show you where the error is. The driver's parser says what it expected and never says where, so TableCore locates the position separately. That probe runs only after the real parser has already rejected the text and never rejects anything itself — a shape it does not understand (extended JSON, ISODate(…), unquoted names) costs you the position, not a false error.

#Cancelling a build

Cancelling is not giving up on waiting. The cancellation token stops the client waiting; the server keeps building. So TableCore also drops the index by name — that is how you interrupt a build in MongoDB.

The drop is best-effort: the build may have finished already, or not started.

#Editing an index

MongoDB has no command that modifies an index. Editing is a drop followed by a create, so TableCore remembers the original specification and tries to restore it when the second half fails.

The result names what actually happened:

The third one is a production incident, not an interface hiccup, and it is reported as such. Restoration drops the fields createIndexes will not accept (v, ns, clustered).

#Text indexes look different when they come back

MongoDB replaces a text index's keys with an _fts/_ftsx pair and moves the fields into weights. TableCore reads the field names back out of weights, so the list shows you the index you created rather than the one the server stores.

#A practical order of work

1. Run the query. It is slow.
2. Explain it. Read documents examined for every row returned.
3. Index the field being filtered, sorted or joined on.
4. Explain again. The ratio should collapse.
5. Come back in a week and check the usage counter, so the index you added is the index being used.

For a $lookup, index the foreign field. An unindexed $lookup reads the foreign collection once per input document and does not appear as a collection scan in the outer plan — TableCore's explain detects it anyway, which is often the first time anyone sees it.