MongoDB Arrays, Indexes, Ordering and Query Planning

MongoDB array query

A MongoDB query can match array membership, element conditions, exact arrays or multiple conditions on the same element with $elemMatch.

multikey index

MongoDB creates a multikey index when an indexed field contains arrays.

compound index

A compound index orders entries by multiple fields; its field order determines usable prefixes and its support for filters and sorting.

index prefix

The leading field sequence of a compound index that determines which query and sort patterns can use it efficiently.

sort and index

An index can provide requested order when its key sequence and directions are compatible with the query predicates and sort.

query planner

The DBMS component that compares valid execution strategies and selects a plan using statistics and estimated cost.

explain plan

A representation of the access paths, join methods, operator order and estimates chosen for a query.

covered query

A covered query is answered entirely from index entries because all filtered and returned fields are contained in the index.

index selectivity

The ability of an index key to narrow a query to a small fraction of records; higher selectivity usually reduces examined entries.

MongoDB performance

MongoDB performance depends on suitable schema and indexes, selective queries, bounded document growth and verified execution plans.

Multikey Indexes

Indexing an array field creates a multikey index with entries derived from array elements. This supports array membership queries.

Compound multikey indexes have restrictions when more than one indexed path is an array. Arrays with many elements can produce many index entries and high write cost.

Use $elemMatch where predicates must apply to the same array element. Index design and query semantics must agree.

Prefix Indexes

MySQL can index a prefix of a long string:

CREATE INDEX idx_customer_email_prefix ON customer(email(20));

This reduces index size but can decrease selectivity and cannot enforce full-value uniqueness unless the indexed prefix itself is guaranteed unique.

Choose prefix length from data-distribution measurements, not guesswork. Modern limits depend on engine, row format, character set and version.

Explain and Query Plans

Use explain to inspect access:

db.products.find({ status: "ACTIVE", price: {$gte: NumberDecimal("100.00")} }).explain("executionStats")

Examine index scans, collection scans, keys examined, documents examined, returned rows and execution stages.

An index name alone does not prove efficiency. A broad scan of index keys followed by many document fetches can still be expensive.

An Index Design Method

For an important query:

Index design is workload design. The best index is not the one with the most columns; it is the smallest maintainable structure that measurably supports important correct queries.

Indexes

Create an index:

db.products.createIndex({status: 1, price: -1})

This can support equality on status followed by a compatible price range or order. Compound index prefixes matter, much like ordered relational indexes.

MongoDB index types include single-field, compound, multikey for arrays, text, geospatial, hashed, wildcard, sparse, partial, TTL and unique variants.

Every index consumes storage and write work. Build indexes from measured access patterns.

EXPLAIN

EXPLAIN shows the chosen plan:

EXPLAIN SELECT employee_id, salary FROM employee WHERE department_id = 10 AND salary >= 50000;

Important fields can include access type, possible keys, selected key, key length, estimated rows, join order and extra operations.

Read the plan as a whole. Seeing an index name does not prove efficiency; it might scan the entire index, examine many rows, sort a large intermediate result or perform costly lookups.

Covering Indexes

An index covers a query when it contains every column needed for filtering and output:

CREATE INDEX idx_order_customer_date_total ON order_header(customer_id, ordered_at, total_amount);

For a compatible query, InnoDB may answer from secondary index entries without fetching full clustered rows. EXPLAIN may report an index-only access indicator such as “Using index.”

Coverage is query-specific. Adding every output column makes indexes wide and expensive to maintain, so covering design should target important measured workloads.

Selectivity

Selectivity describes how strongly a predicate narrows candidates. A unique identifier is highly selective; a Boolean status may match half the table.

An index on a low-cardinality column can still help when one value is rare, when combined with other columns or when it covers the query. It may be useless when the predicate returns most rows.

The optimizer estimates selectivity from statistics and data distribution. “This column has an index” does not imply that using it is cheapest.

Query Shape Before Indexes

Indexes cannot repair an incorrect query. First verify join conditions, result grain, NULL behavior and predicates.

Reduce unnecessary columns and rows, pre-aggregate many-side data when appropriate, avoid repeated correlated work and use EXISTS for existence. Then index the resulting stable access patterns.

Adding indexes to compensate for SELECT *, accidental cross joins or non-sargable conversions creates maintenance cost without addressing the root problem.

Primary, Unique and Ordinary Indexes

A primary-key index enforces primary identity. A unique index enforces uniqueness of its key values under MySQL's NULL rules. An ordinary index supplies an access path without a uniqueness rule.

Constraint and performance roles overlap but are not identical. Do not remove a required unique constraint merely because no current query appears to use its index. It protects data integrity.

Foreign-key columns often benefit from indexes because parent checks, child lookups, joins and referential actions use them. MySQL also imposes index requirements for enforced foreign keys.

Sorting and Grouping

An index can supply rows in an order compatible with ORDER BY and avoid a separate sort:

WHERE customer_id = ? ORDER BY ordered_at DESC, order_id DESC

A matching index might begin with customer_id, ordered_at, order_id in compatible directions and version-supported form.

If filtering and ordering requirements conflict, the optimizer chooses a tradeoff. LIMIT makes ordered index access especially valuable when it can stop early.

Only ORDER BY defines the result-order contract, even if the selected plan happens to scan an index.

The Leftmost-Prefix Principle

For index (a, b, c), useful ordered prefixes are:

(a) (a, b) (a, b, c)

A query using a and c but not b may use a effectively but cannot usually use c as the next continuous ordered component.

Column order should reflect important predicates, joins, ordering, selectivity and reuse across real queries.

Indexes as Access Structures

An index is an auxiliary structure that helps the DBMS locate rows without examining every table record. It stores indexed key values with information that leads to corresponding rows.

CREATE INDEX idx_employee_department ON employee(department_id);

The index can support queries that search, join, group or order by department_id. It does not change relational meaning and does not guarantee output order without ORDER BY.

An index trades faster access for storage, maintenance during writes, memory use and administrative work.

Join Indexes

For a join:

FROM order_header AS o JOIN order_line AS l ON l.order_id = o.order_id

an index on order_line(order_id) supports finding lines for each order. The order_header primary key already supports the opposite lookup.

Composite indexes can include additional join filters:

order_line(order_id, product_id)

Choose direction from likely driving tables and predicates. Indexing every join column separately may be inferior to a composite index matching the full access pattern.

Index Write Costs

Each INSERT must add entries to every relevant index. UPDATE must change indexes containing modified keys. DELETE must remove entries. More and wider indexes increase:

• write I/O and CPU;
• transaction duration;
• buffer-pool use;
• lock and latch pressure;
• storage and backup size;
• schema-change time.

Duplicate or unused indexes should be identified through evidence and removed carefully after checking constraint roles and workload history.

EXPLAIN ANALYZE

Where supported, EXPLAIN ANALYZE executes the query and reports actual timing and row counts alongside estimates.

Large differences between estimated and actual rows reveal poor statistics, skew or model limitations. Because the query executes, use care with cost and with statements that can modify data.

Run tests with representative parameter values and data volume. A plan fast for one selective customer may be poor for another customer owning millions of rows.

B-Tree-Style Indexes

MySQL commonly uses B-tree-style indexes. Their balanced ordered structure supports:

• equality lookup;
• ordered range lookup;
• prefix traversal of composite keys;
• minimum and maximum access;
• ordered scans that may satisfy ORDER BY;
• grouping in compatible key order.

A range such as salary BETWEEN 50000 AND 70000 maps naturally to an interval of ordered keys. Hash-like structures suit equality but do not naturally support ordered ranges.

Composite Indexes

A composite index contains several columns in a defined order:

CREATE INDEX idx_employee_dept_salary ON employee(department_id, salary);

It naturally orders first by department_id and then by salary within each department. It can support:

WHERE department_id = 10

and:

WHERE department_id = 10 AND salary >= 50000

It usually cannot provide the same direct ordered search for salary alone because salary is not the leading column.

Sargable Predicates

A sargable predicate can be translated into a direct index search argument. Equality, bounded ranges and prefix LIKE patterns often qualify.

Common obstacles include:

Rewrite without changing semantics. A computed indexed column may be appropriate when the transformed value is a frequent search key.

Why a Table Scan Can Be Correct

An index lookup involves traversing the index and possibly performing many primary-row lookups. When a query returns a large percentage of the table, sequentially scanning pages can be cheaper.

Small tables also make scans inexpensive. A scan is not automatically a performance defect.

Judge the plan from rows examined, I/O pattern, latency, concurrency and workload frequency. Forcing an index without evidence can make a query slower and more fragile as data distribution changes.

Pagination

Offset pagination:

ORDER BY ordered_at DESC, order_id DESC LIMIT 20 OFFSET 100000

may scan and discard many prior rows.

Keyset pagination continues after the last seen key:

WHERE (ordered_at, order_id) < (?, ?) ORDER BY ordered_at DESC, order_id DESC LIMIT 20

With a matching index, cost stays closer to page size. It also avoids shifts caused by newly inserted earlier rows. The ordering must include a unique tie-breaker.

Arrays

A scalar equality condition can match an array containing that value:

db.products.find({tags: "database"})

$all requires the array to contain every specified value:

{tags: {$all: ["database", "design"]}}

$size tests exact array length. $elemMatch requires one array element to satisfy several conditions:

{ reviews: { $elemMatch: {rating: {$gte: 4}, verified: true} } }

Without $elemMatch, separate dotted predicates might be satisfied by different array elements.

Measuring Performance

Measure representative workloads with realistic data volume, distribution, concurrency and parameter values. Record latency percentiles, rows examined, I/O, lock waits, CPU and execution frequency.

A query executed once per day can tolerate a plan that would be unacceptable thousands of times per second. A microbenchmark on an empty development table does not predict production behavior.

Change one design assumption at a time, re-check plans and include write cost in the result.

Statistics

Statistics estimate table sizes, value distributions and selectivity. MySQL can refresh statistics through analysis operations and newer versions support histograms for selected columns.

Maintenance should respond to evidence of stale or inaccurate estimates. Refreshing statistics can change plans, so important queries should be observed afterward.

Statistics describe individual distributions imperfectly and may not capture correlation between columns. Composite indexes and schema constraints can provide more useful structure.

Functional and Generated-Column Indexing

A predicate that applies a function may not use an ordinary index on the original column:

WHERE LOWER(email) = 'a@example.com'

Supported MySQL versions can index expressions or generated columns. Another solution is a collation whose comparison rules already match the requirement.

For dates, a range is often clearer:

WHERE ordered_at >= '2026-01-01' AND ordered_at < '2027-01-01'

rather than YEAR(ordered_at) = 2026.

The Query Optimizer

The optimizer considers access methods, indexes, join orders, join algorithms, predicate placement, sorting, grouping and materialization. It estimates costs using metadata and statistics.

Cost estimates can be wrong when statistics are stale, data is skewed, predicates are correlated or parameter values vary widely.

The optimizer does not understand unstated business assumptions. Correct keys and constraints can improve both integrity and available optimization information.