.. _read-operations-indexing: ================== Query Optimization ================== .. default-domain:: mongodb .. contents:: On this page :local: :backlinks: none :depth: 1 :class: singlecol Indexes improve the efficiency of read operations by reducing the amount of data that query operations need to process. This simplifies the work associated with fulfilling queries within MongoDB. Create an Index to Support Read Operations ------------------------------------------ If your application queries a collection on a particular field or set of fields, then an index on the queried field or a :doc:`compound index ` on the set of fields can prevent the query from scanning the whole collection to find and return the query results. For more information about indexes, see the :doc:`complete documentation of indexes in MongoDB`. .. example:: An application queries the ``inventory`` collection on the ``type`` field. The value of the ``type`` field is user-driven. .. code-block:: javascript var typeValue = ; db.inventory.find( { type: typeValue } ); To improve the performance of this query, add an ascending, or a descending, index to the ``inventory`` collection on the ``type`` field. [#ensureIndexOrder]_ In the :binary:`~bin.mongo` shell, you can create indexes using the :method:`db.collection.ensureIndex()` method: .. code-block:: javascript db.inventory.ensureIndex( { type: 1 } ) This index can prevent the above query on ``type`` from scanning the whole collection to return the results. To analyze the performance of the query with an index, see :doc:`/tutorial/analyze-query-plan`. In addition to optimizing read operations, indexes can support sort operations and allow for a more efficient storage utilization. See :method:`db.collection.ensureIndex()` and :doc:`/administration/indexes` for more information about index creation. .. [#ensureIndexOrder] For single-field indexes, the selection between ascending and descending order is immaterial. For compound indexes, the selection is important. See :ref:`indexing order ` for more details. .. _read-operations-query-selectivity: Query Selectivity ----------------- Query selectivity refers to how well the query predicate excludes or filters out documents in a collection. Query selectivity can determine whether or not queries can use indexes effectively or even use indexes at all. More selective queries match a smaller percentage of documents. For instance, an equality match on the unique ``_id`` field is highly selective as it can match at most one document. Less selective queries match a larger percentage of documents. Less selective queries cannot use indexes effectively or even at all. .. include:: /includes/extracts/inequality_operators_selectivity.rst The selectivity of :query:`regular expressions <$regex>` depends on the expressions themselves. For details, see :ref:`regular expression and index use `. .. _covered-queries: .. _indexes-covered-queries: .. _read-operations-covered-query: Covered Query ------------- An index :ref:`covers ` a query when both of the following apply: - all the fields in the :ref:`query ` are part of an index, **and** - all the fields returned in the results are in the same index. For example, a collection ``inventory`` has the following index on the ``type`` and ``item`` fields: .. code-block:: javascript db.inventory.ensureIndex( { type: 1, item: 1 } ) This index will cover the following operation which queries on the ``type`` and ``item`` fields and returns only the ``item`` field: .. code-block:: javascript db.inventory.find( { type: "food", item:/^c/ }, { item: 1, _id: 0 } ) For the specified index to cover the query, the projection document must explicitly specify ``_id: 0`` to exclude the ``_id`` field from the result since the index does not include the ``_id`` field. Performance ~~~~~~~~~~~ Because the index contains all fields required by the query, MongoDB can both match the :ref:`query conditions ` and return the results using only the index. Querying *only* the index can be much faster than querying documents outside of the index. Index keys are typically smaller than the documents they catalog, and indexes are typically available in RAM or located sequentially on disk. Limitations ~~~~~~~~~~~ An index **cannot** cover a query if: - the query is on a :term:`sharded ` collection and run against a :term:`mongos`. .. versionchanged:: 2.6.4 In earlier versions, an index cannot cover a query on a sharded collection when run against either a :binary:`~bin.mongos` or the primary. - any of the indexed fields in any of the documents in the collection includes an array. If an indexed field is an array, the index becomes a :ref:`multi-key index ` and cannot support a covered query. - any of the indexed field in the query predicate or returned in the projection are fields in embedded documents. [#index-embedded-document-fields]_ For example, consider a collection ``users`` with documents of the following form: .. code-block:: javascript { _id: 1, user: { login: "tester" } } The collection has the following index: .. code-block:: none { "user.login": 1 } The ``{ "user.login": 1 }`` index does **not** cover the following query: .. code-block:: none db.users.find( { "user.login": "tester" }, { "user.login": 1, _id: 0 } ) However, the query can use the ``{ "user.login": 1 }`` index to find matching documents. ``indexOnly`` ~~~~~~~~~~~~~ To determine whether a query is a covered query, use the :method:`~cursor.explain()` method. If the :method:`~cursor.explain()` output displays ``true`` for the :data:`~explain.indexOnly` field, an index covers the query, and MongoDB queries only that index to match the query **and** return the results. For more information see :ref:`indexes-measuring-use`. .. [#index-embedded-document-fields] To index fields in embedded documents, use :term:`dot notation`.