Index lifecycle and health — researched

Creating, naming, listing, hiding and dropping indexes, index definitions and key patterns, and the failure modes: unused and redundant indexes, write overhead, index size and rebuilds. Child pack of Indexing.

Definitions

An index range is a description of which documents Convex should consider when running the query. [source]
Rolling index builds are an alternative to [default index builds.](https://www.mongodb.com/docs/manual/core/index-creation/#std-label-index-operations) [source]

Structure and components

| Parameter | Type | Description | |---|---|---| | `keyPatterns` | document | An array containing index specification documents. Each document contains field and value pairs where the field is the index key and the value describes the type of index for that field. For an ascending index on a field, specify a value of `1` ; for descending index, specify a value of`-1` . .. include:: /includes/indexes/wildcard-use-wc-methods.rst | | `options` | document | Optional. A document that contains a set of options that controls the creation of the indexes. See [Options](https://www.mongodb.com#std-label [source]
Optional filter expression made up of `q.or` and `q.eq` operating over the filter fields of the index. [source]
1. Create indexes on chemical structure columns: [source]
This is an object mapping each index name to the config for the index. [source]
This is an object mapping index names to index field paths. [source]
- Name of the cluster on which the rolling index build failed - Namespace on which the rolling index build failed - Project that contains the cluster and namespace - Organization that contains the project - Link to the [activity feed event](https://www.mongodb.com/docs/atlas/tutorial/activity-feed/#std-label-view-activity-feed) [source]
The `location` field is an embedded document that contains the embedded fields `city` and `state`. Create an index on the `location.state` field: [source]
(https://www.mongodb.com/docs/manual/core/transactions/) | | `ns` | document | The namespace (database and or collection) affected by the event. | | `ns.db` | string | The name of the database where the event occurred. | | `ns.coll` | string | The name of the collection where the event occurred. | | `operationDescription` | document | Additional information on the change operation. This document and its subfields only appears when the change stream uses [expanded events.](https://www.mongodb.com/docs/manual/reference/change-events/#std-label-change-streams-expanded-events) *New in version 6.0. [source]
- `listIndexes.cursor`- A result set returned in the batch size specified by your cursor. Each document in the batch output contains the following fields: FieldTypeDescriptionid integer A 64-bit integer. If zero, there are no more batches of information. If non-zero, a cursor ID, usable in a `getMore` command to get the next batch of index information.ns string The database and collection name in the following format: `<database-name>.<collection-name>`firstBatch document Index information includes the keys and options used to create the index. The index option hidden is only present if the va [source]
rd/#std-label-wildcard-index-core) | | `options` | document | Optional. A document that contains a set of options that controls the creation of the index. See [Options](https://www.mongodb.com#std-label-ensureIndex-options) for details. | | | integer or string | Optional. The minimum number of data-bearing voting replica set members (i.e. commit quorum), including the primary, that must report a successful [index build](https://www.mongodb.com/docs/manual/core/index-creation/#std-label-index-operations-replicated-build) before the primary marks the`indexes` as ready. A "voting" member is any r [source]
Test out your new index definition by inserting two users that do not contain the fields `accounts.bank` and `accounts.number`: [source]

How it works

| Name | Type | Description [source]
When a single unqualified name is given, Turso resolves it in the order **collation → table → index**: if the name matches a collation sequence, all indexes using that collation are rebuilt; otherwise it is treated as a table or index name. [source]
The `prisma` relation mode does not use foreign keys, so no indexes are created when you use Prisma Migrate or `db push` to apply changes to your database. You instead need to manually add an index on your relation scalar fields with the [`@@index`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference#index) attribute (or the [`@unique`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference#unique), [`@@unique`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference) or [`@@id`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference) attri [source]
The Convex TypeScript types in the `withIndex` make this clear because they require that you compare index fields in order. Because the index is defined on `["author", "title"]`, you must first compare the `author` with `.eq` before the `title`. [source]
When introspecting a database, the `map` argument will *only* be rendered in the schema if the name *differs* from Prisma ORM's [default constraint naming convention for indexes and constraints](#prisma-orms-default-naming-conventions-for-indexes-and-constraints). [source]
Prisma ORM naming convention was chosen to align with PostgreSQL since it is deterministic. It also helps to maximize the amount of times where names do not need to be rendered because many databases out there they already align with the convention. [source]
Prisma ORM always uses the database names of entities when generating the default index and constraint names. If a model is remapped to a different name in the data model via `@@map` or `@map`, the default name generation will still take the name of the *table* in the database as input. The same is true for fields and *columns*. [source]
When no explicit names are provided via `map` arguments Prisma ORM will generate index and constraint names following the [default naming convention](#prisma-orms-default-naming-conventions-for-indexes-and-constraints). [source]
If you introspect a database the names for indexes and constraints will be added to your schema unless they follow Prisma ORM's naming convention. If they do, the names are not rendered to keep the schema more readable. When you migrate such a schema Prisma will infer the default names and persist them in the database. [source]
FTS indexes are updated automatically when you modify the underlying table. [source]
* **Same database.** Your Prisma 8 config points at the **same** database and connection string as v6. * **Server version.** Your MongoDB server is on 8.0 or newer. * **Dry run first.** Rehearse the whole flow on a throwaway copy of your database (`contract emit`, `db update --dry-run`, `db update`, `db verify`) and make sure `db verify` passes before you touch production. * **Index parity.** The indexes on each collection (`db.collection.getIndexes()`) match your contract. * **Validators.** Your existing documents pass the strict validators Prisma 8 adds to each collection (see *Good to know* [source]
Building an index in a rolling fashion reduces the resiliency of your cluster and increases index build times. We only recommend using rolling index builds when regular index builds do not meet your needs. [source]
When an index build completes, Atlas generates an [activity feed event](https://www.mongodb.com/docs/atlas/tutorial/activity-feed/#std-label-view-activity-feed) and sends a notification email to the project owner with the following information: [source]
For more information on creating indexes that support your workload, see [Create Indexes to Support Your Queries.](https://www.mongodb.com/docs/manual/data-modeling/schema-design-process/create-indexes/#std-label-create-indexes-to-support-queries) [source]
Collation-aware index keys might be larger than index keys for indexes without collation because indexes that are configured with collation use ICU collation keys to achieve sort order. [source]
- `dropIndexes`- *New in version 6.0.* :A `dropIndexes` event occurs when an index is dropped from the collection and the change stream has the[showExpandedEvents](https://www.mongodb.com/docs/manual/reference/change-events/#std-label-change-streams-expanded-events) option set to`true` . [source]
- `cursor.min()`- ## Important**mongosh Method**This page documents a [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#mongodb-binary-bin.mongosh) method. This is*not* the documentation for a language-specific driver, such as Node.js.For MongoDB API drivers, refer to the language-specific [MongoDB driver documentation.](https://www.mongodb.com/docs/drivers/)Specifies the *inclusive* lower bound for a specific index in order to constrain the results of[`find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find/#mongodb-method-db.collection.find) .[`min()`](https:// [source]
The [primary](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-primary) marks index build as ready only after a simple majority of data-bearing voting members "vote" to commit the index build. For more information on index builds and the voting process, see [Index Builds in Replicated Environments.](https://www.mongodb.com/docs/manual/core/index-creation/#std-label-index-operations-replicated-build) [source]
When you specify options to [`db.collection.createIndexes()`](https://www.mongodb.com#mongodb-method-db.collection.createIndexes), the options apply to *all* of the specified indexes. For example, if you specify a collation option, all of the created indexes will include that collation. [source]
Leaving the index name field blank causes MongoDB Compass to create a default name for the index. [source]
When run with an index, the query scanned `3` index entries and `3` documents to return `3` matching documents, resulting in a very efficient query. [source]
When the index build completes, shutdown the [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod/#mongodb-binary-bin.mongod) instance. To return the node to its original configuration, undo the configuration changes that you made when you started the node as a standalone. Then, restart the node as a member of the replica set. [source]
The preceding command ensures the correct set of shards is targeted for rolling index builds because no migration for the collection will be allowed to commit. [source]
When the index build completes, shutdown the [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod/#mongodb-binary-bin.mongod) instance. Undo the configuration changes made when starting as a standalone to return to its original configuration and restart. [source]
The REINDEX statement deletes and recreates indexes from scratch. It is useful when a collation sequence definition has changed, or to rebuild an index that may have become out of date. [source]
When using [Stable API](https://www.mongodb.com/docs/manual/reference/stable-api/#std-label-stable-api) V1, all [`createIndexes`](https://www.mongodb.com/docs/manual/reference/command/createIndexes/#mongodb-dbcommand-dbcmd.createIndexes) fields are available with the following exceptions: [source]

Parameters and configuration

| Field | Type | Description | |---|---|---| | dropIndexes | String | The name of the collection whose indexes to drop. | | index | string or document or array of strings | The index or indexes to drop. To drop all indexes except the `_id` index and the last remaining shard key index from the collection if one exists, specify`"*"` . To drop a single index, specify either the index name, the index specification document (unless the index is a [text](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/#std-label-index-type-text) index), or an array of the index name. To drop [source]
| Parameter | Type | Description | |---|---|---| | `indexes` | string or document or array of strings | Optional. Specifies the index or indexes to drop. **To drop all but the _id index from the collection** , omit the parameter. **To drop a single index** , specify either the index name, the index specification document (unless the index is a[text](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/#std-label-index-type-text) index), or an array of the index name. To drop a[text](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/#std-label-index-type [source]

How-to and procedures

To drop an index, you need its name. To get all index names for a collection, run the [`getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) method: [source]
To drop a specific index, use the [`dropIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndex/#mongodb-method-db.collection.dropIndex) method and specify the index name: [source]
To drop multiple indexes, use the [`dropIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndexes/#mongodb-method-db.collection.dropIndexes) method and specify an array of index names: [source]
To drop an index, insert the index name and run [db.collection.dropIndex().](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndex/#std-label-collection-drop-index) [source]
3. **Performance Optimization**: * Use word\_similarity() for whole word matching * Create indexes on specific columns rather than all text columns * Monitor index size and rebuild when necessary [source]
Create an index on a table to improve query performance for lookups, joins, and ordering. [source]
You can check the backfill progress via the [*Indexes* pane](/dashboard/deployments/data.md#view-the-indexes-of-a-table) on the dashboard data page. Once it is complete, you can enable the index and use it by removing the `staged` option. [source]
Use the following code to create an index on the `movies` collection of the `sample_mflix` database with the collation locale `"fr"` for string comparisons: [source]
You can use the `map` argument to define **custom constraint and index names** in the underlying database. [source]
To fix this, add an index to your `Post` model: [source]
To implement full-text search on multiple columns, you can create index on multiple columns and concatenate the columns with `to_tsvector` function: [source]
You can create indexes on the `jsonb` fields for better query performance, particularly for multi-tenant systems. [source]
Create an index on one or more columns or expressions to accelerate queries [source]
You can create an index across multiple fields at once, query a specific range of data, and change the order of your query result. [Read the complete index documentation](/database/reading-data/indexes/.md) to learn more. [source]
To add an index onto a table, use the [`index`](/api/classes/server.TableDefinition.md#index) method on your table's schema: [source]
To create a staged index, use the following syntax in your `schema.ts`. [source]
To specify the index name, include the `name` option when you create the index: [source]
You can remove a specific index from a collection. You may need to drop an index if you see a negative performance impact, want to replace it with a new index, or no longer need the index. [source]
To drop all indexes except the `_id` index, use the [`dropIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndexes/#mongodb-method-db.collection.dropIndexes) method: [source]
To confirm that the index was dropped, run the [`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) method: [source]
To create an index, use the [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#mongodb-method-db.collection.createIndex) method. Your operation should resemble this prototype: [source]
You can create indexes on embedded documents as a whole. However, only queries that specify the **entire** embedded document use the index. Queries on a specific field within the document do not use the index. [source]
Create an index on the `location` field: [source]
You can create an index on a single field to improve performance for queries on that field. [source]
To create a single-field index, use the [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#mongodb-method-db.collection.createIndex) method: [source]
Consider a school administrator who frequently looks up students by their GPA. You can create an index on the `gpa` field to improve performance for those queries: [source]
You can create indexes on fields within embedded documents. Indexes on embedded fields can fulfill queries that use [dot notation.](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-dot-notation) [source]
To view the created indexes, run the [`getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) method: [source]
To see the indexes in the `pets` collection, run the [`getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) method: [source]
To drop the index `catIdx`, you can use either the index name: [source]
To confirm that the index was dropped, run the `getIndexes()` method again: [source]
To support the query on the `quantity` field, add an index on the `quantity` field: [source]
Avoid performing rolling index and replicated index build processes concurrently as it might lead to unexpected issues, such as broken builds and crash loops. [source]
Make sure you are not performing [DDL operations](https://www.mongodb.com/docs/manual/reference/ddl-operations/#std-label-ddl-operations) while conducting the rolling index build. [source]
Ensure that equality fields always come first. Placing equality fields first keeps the remaining index fields in sorted order. Choose whether to use a sort or range field next based on your index's specific needs: [source]
To improve query performance, create an index on the `directors` and `year` fields: [source]
To ensure your database adheres to your application design, you can strategically create indexes to combine index properties with schema validation. [source]
To enforce the application’s rules, create an index on the `accounts.bank` and `accounts.number` fields with the following characteristics: [source]
Consider deleting unused indexes to improve application performance. For more information, see [Remove Unnecessary Indexes.](https://www.mongodb.com/docs/manual/data-modeling/design-antipatterns/unnecessary-indexes/#std-label-unnecessary-indexes-antipattern) [source]
To use the `hidden` option with [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#mongodb-method-db.collection.createIndex), you must have [featureCompatibilityVersion](https://www.mongodb.com/docs/manual/reference/command/setFeatureCompatibilityVersion/#std-label-view-fcv) set to `6.0` or greater. [source]
To verify, run [`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) on the `addresses` collection: [source]
To verify, run [`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) on the `restaurants` collection: [source]
Consider the following [`db.collection.createIndex()`](https://www.mongodb.com#mongodb-method-db.collection.createIndex) operation: [source]
use inventory db.products_catalog.createIndexes( [ { "$**" : 1 } ], { "wildcardProjection" : { "product_attributes.colors" : 1, "product_attributes.material" : 1 } } ) [source]
use inventory db.products_catalog.createIndexes( [ { "$**" : 1 } ], { "wildcardProjection" : { "product_attributes.colors" : 0, "product_attributes.material" : 0 } } ) [source]
To run [`db.collection.getIndexes()`](https://www.mongodb.com#mongodb-method-db.collection.getIndexes) when access control is enforced, users must have privileges to [`listIndexes`](https://www.mongodb.com/docs/manual/reference/privilege-actions/#mongodb-authaction-listIndexes) on the collection. [source]

Examples and snippets

For example, if there is an index of messages on `["projectId", "priority"]`, a range searching for "messages in 'myProjectId' with priority at least 100" would look like: [source]
Here is an example of pgfence analyzing a migration that adds an index without `CONCURRENTLY`: [source]

Measurements and reference values

| | Free/Starter | Professional | Business | Enterprise | Notes | | ----------------- | -------------------------------------------------------------------------------------- | -------------------------------------------------- | --------------------- [source]
The architecture supports vertical scaling for tenants and horizontal scaling across tenants. For vector embeddings, the total index size is divided into smaller chunks across multiple machines. Additionally, since the storage is in S3, Nile can swap a tenant’s embeddings entirely to S3 without maintaining a local cache. The indexes themselves are smaller, and multiple machines can be leveraged to build indexes in parallel. This approach provides lower latency and nearly 100% recall by reducing the search space per customer. [source]

Problems, failure modes and limitations

* Corresponding database construct: `INDEX` * There are some additional index configuration options that cannot be provided via the Prisma schema yet. These include: * PostgreSQL and CockroachDB: * Define index fields as expressions (e.g. `CREATE INDEX title ON public."Post"((lower(title)) text_ops);`) * Create indexes concurrently with `CONCURRENTLY` [source]
| Parameter | Description | | ------------- | ------------------------------------------------------------------------------------------------- | | `IF EXISTS` | Prevents an error if the index does not exist. The statement is a no-op when the index is absent. | | `schema-name` | The name of the attached database containing the index. Defaults to the main database if omitted. | | `index-name` | The name of the index to drop. | [source]
- Index names must be unique. Creating an index with the name of an existing index returns an error. - You can't rename an existing index. Instead, you must [drop](https://www.mongodb.com/docs/manual/core/indexes/drop-index/#std-label-drop-an-index) and recreate the index with a new name. [source]
| Parameter | Type | Description | |---|---|---| | `index` | string or document | Required. Specifies the index to drop. You can specify the index either by the index name or by the index specification document. To drop a [text](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/#std-label-index-type-text) index, specify the index name. You cannot specify `"*"` to drop all non-`_id` indexes. Use[`db.collection.dropIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndexes/#mongodb-method-db.collection.dropIndexes) instead. If an index specif [source]
When you insert `account1` for the second time on the user, MongoDB does not create an index entry, so there are no duplicate values on it. To effectively implement your application design, your database should return an error if you attempt to add the same account multiple times to the same user. [source]
Since most databases have a length limit for entity names, the names will be trimmed if necessary to not violate the database limits. We will shorten the part before the `_suffix` as necessary so that the full name is at most the maximum length permitted. [source]
Merges all Tantivy segments into a single optimized segment. This improves query performance and reduces storage overhead, particularly after bulk inserts. [source]
Indexes like `by_foo` and `by_foo_and_bar` are usually redundant (you only need `by_foo_and_bar`). Reducing the number of indexes saves on database storage and reduces the overhead of writing to the table. Each index counts as another copy of the table's documents toward [database storage](/production/state/limits.md#database), so a table with three indexes uses about 4× its document size. [source]
* `CREATE INDEX` without `CONCURRENTLY` (blocks writes) * `ALTER COLUMN TYPE` (full table rewrite with `ACCESS EXCLUSIVE` lock) * `ADD COLUMN ... NOT NULL` without a safe default (blocks reads and writes) * Missing `lock_timeout` settings (risk of lock queue death spirals) [source]
| Risk level | Meaning | | ------------ | ----------------------------------------------------------------------------------------------------------------- | | **LOW** | Safe operations with minimal locking (e.g., `ADD COLUMN` with a constant default on PG 11+) | | **MEDIUM** | Operations that block writes but not reads (e.g., `CREATE INDEX` without `CONCURRENTLY`) | | **HIGH** | Operations that block writes and competing DDL, but [source]
pgfence will flag this as a `MEDIUM` risk because `CREATE INDEX` takes a `SHARE` lock, which blocks all writes to the table for the duration of the index build. It will suggest using `CREATE INDEX CONCURRENTLY` instead. [source]
> [!WARNING] > Prisma Migrate does not generate `CONCURRENTLY` variants automatically. If pgfence flags an index creation, you should manually edit the generated migration SQL file to add `CONCURRENTLY` before applying it. Note that `CREATE INDEX CONCURRENTLY` cannot run inside a transaction, so you will also need to ensure the migration runs outside a transaction block. [source]
pgfence can adjust risk levels based on actual table sizes. A `CREATE INDEX` on a 100-row table is very different from the same operation on a 10-million-row table. [source]
Index fields used in `where`, `orderBy`, and relations. Without indexes, the database can be forced to scan entire tables to find matching rows, which becomes slower as tables grow. [source]
Before running `prisma migrate deploy`, you can analyze your migration SQL files for potentially dangerous patterns using a migration safety tool like [pgfence](https://www.prisma.io/docs/guides/integrations/pgfence). pgfence detects operations that acquire heavy locks (such as `CREATE INDEX` without `CONCURRENTLY` or `ALTER COLUMN TYPE`), reports risk levels, and provides safe rewrite recipes. [source]
Patching or hotfixing a database involves making an often time critical change directly in production. For example, you might add an index directly to a production database to resolve an issue with a slow-running query. [source]
* **No subqueries in expressions**: The ENCODE, DECODE, and DEFAULT expressions cannot contain subqueries, aggregate functions, or window functions. * **No indexes on STRUCT/UNION columns**: CREATE INDEX on a STRUCT or UNION column is not supported. [source]
You may notice that the first deploy that defines an index is a bit slower than normal. This is because Convex needs to *backfill* your index. The more data in your table, the longer it will take Convex to organize it in index order. If you need to add indexes to large tables, use a [staged index](#staged-indexes). [source]
By default, index creation happens synchronously when you deploy code. For large tables, the process of [backfilling the index](/database/reading-data/indexes/indexes-and-query-perf.md#backfilling-and-maintaining-indexes) for the existing table can be slow. Staged indexes are a way to create an index on a large table asynchronously without blocking deploy. This can be useful if you are working on multiple features at once. [source]
Staged indexes cannot be used until enabled [source]
Staged indexes cannot be used in queries until you enable them. To enable them, they must first finish backfilling. [source]
No reserved fields (starting with `_`) are allowed in indexes. The `_creationTime` field is automatically added to the end of every index to ensure a stable ordering. It should not be added explicitly in the index definition, and it's counted towards the index fields limit. [source]
The same is true for Convex indexes! When you define a new index, the first time you run `npx convex deploy` Convex will need to loop through all of your documents and index each one. This is why the first deploy after the creation of a new index will be slightly slower than normal; Convex has to do a bit of work for each document in your table. If the table is particularly large, consider using a [staged index](/database/reading-data/indexes/.md#staged-indexes) to complete the backfill asynchronously from the deploy. [source]
* Table and index names must be valid identifiers and cannot start with an underscore. [source]
If you decide to continue with a rolling index, consider that they must meet certain conditions to succeed. To ensure your index build succeeds, avoid the following design patterns that commonly trigger a restart loop: [source]
For workloads which cannot tolerate performance decrease due to index builds, consider building indexes in a rolling fashion. [source]
Atlas automatically cancels rolling index builds that don't succeed on all nodes. When a rolling index build completes on some nodes, but fails on others, Atlas cancels the build and removes the index from any nodes that it was successfully built on. [source]
[Unique](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#options-for-all-index-types) [index options](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#options) are incompatible with building indexes in a rolling fashion. If you specify `unique` in the Options pane, Atlas rejects your configuration with an error message. [source]
Additionally, if there is a collation on the index key, you can only ensure uniqueness if the collation is simple. [source]
If your query specifies `$elemMatch` on fields that diverge from a common path, MongoDB **cannot** compound the bounds of index keys from the same array. [source]
Starting in MongoDB 7.1, index builds are improved with faster error reporting and increased failure resilience. You can also set the minimum available disk space required for index builds using the new [`indexBuildMinAvailableDiskSpaceMB`](https://www.mongodb.com/docs/manual/reference/parameters/#mongodb-parameter-param.indexBuildMinAvailableDiskSpaceMB) parameter, which stops index builds if disk space is too low. [source]
crash. A request to stop an index build is not always possible: if a member has already voted to commit the index, then the secondary cannot request that the index build stop and the secondary crashes (similar to MongoDB 7.0 and earlier). | An index build error can cause a secondary member to crash. | | Improved disk space management for index builds. An index build may be automatically stopped if the available disk space is below the minimum specified in the [`indexBuildMinAvailableDiskSpaceMB`](https://www.mongodb.com/docs/manual/reference/parameters/#mongodb-parameter-param.indexBuildMinAva [source]
er is any replica set member where[`members\[n\].votes`](https://www.mongodb.com/docs/manual/reference/replica-configuration/#mongodb-rsconf-rsconf.members-n-.votes) is greater than`0` . Supports the following values: `"votingMembers"` - all data-bearing voting replica set members (*Default* ). `"majority"` - a simple majority of data-bearing voting replica set members. `<int>` - a specific number of data-bearing voting replica set members. `0` - Disables quorum-voting behavior. Members start the index build simultaneously but do*not* vote or wait for quorum before completing the index build. [source]
[`db.collection.createIndexes()`](https://www.mongodb.com#mongodb-method-db.collection.createIndexes) will return an error if you attempt to create indexes with incompatible options or too many arguments. Refer to the option descriptions for more information. [source]
- When a user is creating an index with a `unique` key constraint and one shard contains a chunk with duplicate documents. In such cases, the create index operation may succeed on the shards without duplicates but not on the shard with duplicates. - When a user is creating an index across the shards in a rolling manner but either fails to build the index for an associated shard or incorrectly builds an index with different specification. [source]
"Range" filters scan fields. The scan doesn't require an exact match, which means range filters are loosely bound to index keys. To improve query efficiency, limit the range bounds and use equality matches to reduce the number of documents to scan. [source]
A single collection can have a maximum of 64 indexes. However, too many indexes can degrade performance before that limit is reached. For collections with a high write-to-read ratio, indexes can degrade performance because each insert must also update any indexes. [source]
large number of indexes.## Warning - [`reIndex`](https://www.mongodb.com#mongodb-dbcommand-dbcmd.reIndex) may only be run on[standalone](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-standalone) instances. - For most users, the [`reIndex`](https://www.mongodb.com#mongodb-dbcommand-dbcmd.reIndex) command is unnecessary. [source]

Facts and statements

Assume you want to add an index for the `title` field of the `Post` model [source]
The following schema defines three constraints (`@id`, `@unique`, and `@relation`) and one index (`@@index`): [source]
The following example adds custom names to one `@id` and the `@@index`: [source]
Additionally to `map`, the `@@id` and `@@unique` attributes take an optional `name` argument that allows you to customize your Prisma Client API. [source]
Builder to define an index range to query. [source]
This class is designed to only allow you to specify ranges that Convex can efficiently use your index to find. For all other filtering use [filter](/api/interfaces/server.OrderedQuery.md#filter). [source]
| Name | Type | Description | | ----------- | ----------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------- | | `fieldName` | `IndexFields`\[`FieldNum`] | The name of the field to compare. Must be the next field i [source]
| Name | Type | Description | | ----------- | ----------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fieldName` | `Ind [source]
`.index("by_foo", ["foo"])` is really an index on the properties `foo` and `_creationTime`, while `.index("by_foo_and_bar", ["foo", "bar"])` is an index on the properties `foo`, `bar`, and `_creationTime`. If you have queries that need to be sorted by `foo` and then `_creationTime`, then you need both indexes. [source]
For example, `.index("by_channel", ["channel"])` on a table of messages can be used to query for the most recent messages in a channel, but `.index("by_channel_and_author", ["channel", "author"])` could not be used for this since it would first sort the messages by `author`. [source]
<Step title="Create an Index"> Create an index using the `libsql_vector_idx` function: [source]
```sql theme={null} CREATE INDEX movies_idx ON movies(libsql_vector_idx(embedding)); ``` [source]
* [CREATE INDEX](/sql-reference/statements/create-index) for creating indexes * [DROP INDEX](/sql-reference/statements/drop-index) for removing indexes * [ANALYZE](/sql-reference/statements/analyze) for collecting index statistics [source]
| Constraint or index | Follows convention | Underlying constraint or index names | | ---------------------------------- | ------------------ | ------------------------------------ | | `@id` (on `User` > `id` field) | Yes | `User_pk` | | `@@index` (on `Post`) | Yes | `Post_title_authorName_idx` | | `@id` (on `Post` > `id` field) | Yes | `Post_pk` | | `@relation` (on `Post` > `author`) | Yes | `Post_authorName_fkey` | [source]
| Constraint or index | Follows convention | Underlying constraint or index names | | ---------------------------------- | ------------------ | ------------------------------------ | | `@id` (on `User` > `id` field) | No | `Custom_Primary_Key_Constraint_Name` | | `@@index` (on `Post`) | No | `My_Custom_Index_Name` | | `@id` (on `Post` > `id` field) | Yes | `Post_pk` | | `@relation` (on `Post` > `author`) | Yes | `Post_authorName_fkey` | [source]
Starting in MongoDB 5.0, [unique sparse](https://www.mongodb.com/docs/manual/core/index-sparse/#std-label-sparse-unique-index) and [unique non-sparse](https://www.mongodb.com/docs/manual/core/indexes/index-properties/#std-label-unique-index) indexes with the same [key pattern](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndexes/#std-label-key_patterns) can exist on a single collection. [source]
After you create the index, you can use the [`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) method to get the index name: [source]
- To drop all non- `_id` indexes , specify`"*"` for the`index` .db.runCommand( { dropIndexes: "collection", index: "*" } ) - To drop a single index, issue the command by specifying the name of the index you want to drop. For example, to drop the index named `age_1` , use the following command:db.runCommand( { dropIndexes: "collection", index: "age_1" }) [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#mongodb-binary-bin.mongosh) provides the helper methods[`db.collection.dropIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndex/#mongodb-method-db.collect [source]
[`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#mongodb-method-db.collection.createIndex) for examples of various index specifications. [source]
an array of index names: db.collection.dropIndexes( [ "a_1_b_1", "a_1", "a_1__id_-1" ] ) If the array of index names includes a non-existent index, the method errors without dropping any of the specified indexes. ## TipTo get the names of the indexes, use the [`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.getIndexes/#mongodb-method-db.collection.getIndexes) method. [source]
If you add an index on `status` and query for `{ "status": "pending", "product_type": "electronics" }`, MongoDB must read three index keys, retrieve three documents matching that status, and filter those documents further on `product_type` to return the one matching document. Similarly, a query for `{ "status": {$in: ["processed", "pending"] }, "product_type" : "electronics" }` must read six documents to return the two matching documents. [source]
| Form | Description | | ------------------------ | ---------------------------------------------------------- | | `REINDEX` | Rebuild all indexes in all attached databases | | `REINDEX collation-name` | Rebuild every index that uses the named collation sequence | | `REINDEX table-name` | Rebuild all indexes associated with the named table | | `REINDEX index-name` | Rebuild the named index | [source]
Specifying `@@id([firstName, lastName], name: "fullName")` will change the Prisma Client API to this instead: [source]
The most common form indexes one or more columns by name. [source]
Turso supports a `USING` clause to specify an alternative index method. [source]
Once an FTS index exists, use the `search()` function to query it: [source]
Remove an index from the database. The underlying table and its data are not affected. [source]
This must have the same length as the `dimensions` of the index. This vector search will return the IDs of the documents most similar to this vector. [source]
The names of indexes in a table for a given [GenericTableInfo](/api/modules/server.md#generictableinfo). [source]
1 db.runCommand ( 2 { 3 listIndexes: "contacts" 4 } 5 ) [source]
The following example creates two indexes on the `products` collection: an ascending index on the `manufacturer` field and an ascending index on the `category` field. Both indexes use a [collation](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#std-label-create-index-collation) that specifies the locale `fr` and comparison strength `2`: [source]
For queries or sort operations on the indexed keys that uses the same collation rules, MongoDB can use the index. For details, see [Collation and Index Use.](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#std-label-createIndex-collation-index-use) [source]
const specification = { "accounts.bank": 1, "accounts.number": 1 }; const options = { name: "Unique Account", unique: true }; db.users.createIndex(specification, options); // Unique Account [source]
This creates an index optimized for vector similarity searches on the `embedding` column. [source]
<Step title="Query the indexed table"> ```sql theme={null} SELECT title, year FROM vector_top_k('movies_idx', vector32('[0.064, 0.777, 0.661, 0.687]'), 3) JOIN movies ON movies.rowid = id WHERE year >= 2020; ``` [source]
This query uses the `vector_top_k` [table-valued function](https://www.sqlite.org/vtab.html#table_valued_functions) to efficiently find the top 3 most similar vectors to `[0.064, 0.777, 0.661, 0.687]` using the index. </Step> </Steps> [source]
| Operation | FTS behavior | | --------- | ----------------------------------------------------------------------- | | `INSERT` | New rows are indexed immediately (batched commits every 1000 documents) | | `UPDATE` | Implemented as DELETE + INSERT internally | | `DELETE` | Marks documents as deleted via tombstones, cleaned up on OPTIMIZE | [source]
| Name | Type | | ------------- | ------------------------------------------------------------------------- | | `Document` | extends [`GenericDocument`](/api/modules/server.md#genericdocument) | | `IndexFields` | extends [`GenericIndexFields`](/api/modules/server.md#genericindexfields) | | `FieldNum` | extends `number` = `0` | [source]
* `LowerBoundIndexRangeBuilder`<`Document`, `IndexFields`\[`FieldNum`]> [source]
↳ **`IndexRangeBuilder`** [source]
▸ **eq**(`fieldName`, `value`): `NextIndexRangeBuilder`<`Document`, `IndexFields`, `FieldNum`> [source]
`NextIndexRangeBuilder`<`Document`, `IndexFields`, `FieldNum`> [source]
▸ **gt**(`fieldName`, `value`): `UpperBoundIndexRangeBuilder`<`Document`, `IndexFields`\[`FieldNum`]> [source]
`UpperBoundIndexRangeBuilder`<`Document`, `IndexFields`\[`FieldNum`]> [source]
LowerBoundIndexRangeBuilder.gt [source]
▸ **gte**(`fieldName`, `value`): `UpperBoundIndexRangeBuilder`<`Document`, `IndexFields`\[`FieldNum`]> [source]
LowerBoundIndexRangeBuilder.gte [source]
LowerBoundIndexRangeBuilder.lt [source]
LowerBoundIndexRangeBuilder.lte [source]
Look through your indexes, either in your `schema.ts` file or in the dashboard, and look for any indexes where one is a prefix of another. [source]
For anything the operation factories don't cover (enabling an extension, `CREATE INDEX CONCURRENTLY`, a vendor-specific statement), use `rawSql`, and keep the same three-phase safety if you can: [source]
Introspection read an unrecognized or malformed database shape — an unknown referential action rule, or a malformed index reloption entry. Raised by the Postgres and SQLite control adapters. Meta: `rule`, `entry`, `indexName`. [source]
An authored wire-name prefix (an index name, an RLS policy prefix, or a check's `name:` prefix) exceeds the 54-byte maximum — Postgres identifiers cap at 63 bytes and the wire name appends a 9-byte `_<8hex>` content-hash suffix. Raised at contract lowering. Meta: `prefix`, `maxBytes`. [source]
Prisma Migrate is able to create constraints and indexes with the `length` argument if specified in your data model. This means that you can create indexes and constraints on values of Prisma schema type `Byte` and `String`. If you don't specify the argument the index is treated as covering the full value as before. [source]
See [custom index names](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/database-mapping#constraint-and-index-names) for naming customization. [source]
// `.on()` index('name') .on(table.column1.asc(), table.column2.nullsFirst(), ...) .concurrently() .where(sql``) .with({ fillfactor: '70' }) [source]
await db.run(sql` CREATE INDEX IF NOT EXISTS vector_index ON vector_table(vector) USING vector_cosine(3) `); ``` </Step> [source]
Show index names, optionally filtered by table. [source]
| Feature | Description | | ------------------------------------------------------------------------------- | -------------------------------------------------------- | | [CREATE TYPE](/sql-reference/statements/create-type) | User-defined types for STRICT tables | | [CREATE MATERIALIZED VIEW](/sql-reference/statements/create-materialized-view) | Live materialized views with incremental maintenance | | [BEGIN CONCURRENT](/sql-re [source]
FTS indexes are created with the `USING fts` clause on `CREATE INDEX`. Each indexed column participates in the full-text search. [source]
* [CREATE INDEX](/sql-reference/statements/create-index) for the full `CREATE INDEX ... USING fts` syntax * [Vector Functions](/sql-reference/functions/vector) for similarity search with embeddings [source]
* [CREATE INDEX](/sql-reference/statements/create-index) for index types including FTS * [Data Types](/sql-reference/data-types) for how BLOBs are handled in Turso [source]
<Info> The new table name must not collide with an existing table, view, or index name in the same database. </Info> [source]
| Form | Description | | --------------------- | -------------------------------------------------------- | | `ANALYZE` | Analyze all tables and indexes in all attached databases | | `ANALYZE schema-name` | Analyze all tables and indexes in the named database | | `ANALYZE table-name` | Analyze all indexes on the named table | | `ANALYZE index-name` | Analyze the named index | [source]
* [ALTER TABLE](/sql-reference/statements/alter-table) for modifying existing tables * [DROP TABLE](/sql-reference/statements/drop-table) for removing tables * [Data Types](/sql-reference/data-types) for type affinity and STRICT table types * [CREATE INDEX](/sql-reference/statements/create-index) for indexing table columns * [CREATE TYPE](/sql-reference/statements/create-type) for custom types in STRICT tables * [INSERT](/sql-reference/statements/insert) for adding rows to a table [source]
The `<` operator is special: it controls how values of this type are sorted in ORDER BY, MIN, MAX, and CREATE INDEX. [source]
CDC also tracks DDL operations (CREATE TABLE, DROP TABLE, CREATE INDEX, etc.) as changes to the `sqlite_schema` table: [source]
It's an object mapping each index name to the fields in the index. [source]
This will be an object mapping index names to the fields in the index. [source]
Ƭ **IndexNames**<`TableInfo`>: keyof [`Indexes`](/api/modules/server.md#indexes)<`TableInfo`> [source]
Ƭ **NamedIndex**<`TableInfo`, `IndexName`>: [`Indexes`](/api/modules/server.md#indexes)<`TableInfo`>\[`IndexName`] [source]
**Best practice:** Always include all index fields in the index name. For example, an index on `["field1", "field2"]` should be named `"by_field1_field2"`. [source]
Similarly, even after an index is defined, Convex will have to do a bit of extra work to keep this index up to date as the data changes. Every time a document is inserted, updated, or deleted in an indexed table, Convex will also update its index entry. This is analogous to a librarian creating new index cards for new books as they add them to the library. [source]
Focusing on the second step, the `vectorSearch` API takes in the table name, the index name, and finally a [`VectorSearchQuery`](/api/interfaces/server.VectorSearchQuery.md) object describing the search. This object has the following fields: [source]
In the event of a rolling index build cancellation, Atlas generates an [activity feed event](https://www.mongodb.com/docs/atlas/tutorial/activity-feed/#std-label-view-activity-feed) and sends a notification email to the project owner with the following information: [source]
- Completion date of the index build - Name of the cluster on which the index build completed - Namespace on which the index build completed - Project containing the cluster and namespace - Organization containing the project - Link to the [activity feed event](https://www.mongodb.com/docs/atlas/tutorial/activity-feed/#std-label-view-activity-feed) [source]
- To learn how to create an index, see [Create an Index.](https://www.mongodb.com/docs/manual/core/indexes/create-index/#std-label-manual-create-an-index) - For more information about index properties, see [Index Properties.](https://www.mongodb.com/docs/manual/core/indexes/index-properties/#std-label-index-properties) [source]
If you drop an index that's actively used in production, you may experience performance degradation. Before you drop an index, consider [hiding the index](https://www.mongodb.com/docs/manual/core/index-hidden/#std-label-index-type-hidden) to evaluate the potential impact of the drop. [source]
After you drop an index, the system returns information about the status of the operation. [source]
The dropped index no longer appears in the `getIndexes()` output. [source]
You regularly run a query that returns students with at least one `test_score` greater than `90`. You can create an index on the `test_scores` field to improve performance for this query. [source]
MongoDB compounds the bounds for the `item` key with either the bounds for `"ratings.score"` or the bounds for `"ratings.by"`, depending upon the query predicates and the index key values. MongoDB does not guarantee which bounds it compounds with the `item` field. [source]
- The index keys must share the same field path up to but excluding the field names. - The query must specify predicates on the fields using `$elemMatch` on that path. [source]
For a field in an embedded document, the [dotted field name](https://www.mongodb.com/docs/manual/core/document/#std-label-document-dot-notation), such as `"a.b.c.d"`, is the field path for `d`. To compound the bounds for index keys from the same array, the `$elemMatch` must be on the path up to *but excluding* the field name itself (meaning `"a.b.c"`). [source]
The following example shows how MongoDB combines bounds for index keys from the same array. This example uses the `survey2` collection used in the [previous example.](https://www.mongodb.com#std-label-index-bounds-example-non-array-multiple-array) [source]
In order for a [dot notation](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-dot-notation) query to use an index, you must create an index on the specific embedded field you are querying, not the entire embedded object. For an example, see [Create an Index on an Embedded Field.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-single/create-single-field-index/#std-label-index-embedded-fields) [source]
- An ascending or descending index key on the `department` field - A `text` index key on the`description` field [source]
After you know which fields your application frequently queries, you can create indexes to support queries on those fields. For more information, see [Examples.](https://www.mongodb.com#std-label-schema-design-indexes-examples) [source]
If your application only queries on a single key in a given collection, then you need to create a single-key index for that collection. For example, you can create an index on `title` in the `movies` collection: [source]
{ "_id": { <ResumeToken> }, "operationType": "dropIndexes", "clusterTime": <Timestamp> "collectionUUID": <uuid>, "wallTime": <isodate>, "ns": { "db": "test", "coll": "authors" }, "operationDescription": { "indexes": [ { "v": 2, "key": { "name": 1 }, "name": "name_1" } ] } } [source]
- `dropIndexes`- *Changed in version 6.0.* :The [`dropIndexes`](https://www.mongodb.com#mongodb-dbcommand-dbcmd.dropIndexes) command drops one or more indexes (except the index on the`_id` field and the last remaining shard key index, if one exists) from the specified collection.## TipIn [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#std-program-mongosh) , this command can also be run through the[`db.collection.dropIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndex/#mongodb-method-db.collection.dropIndex) and[`db.collection.dropIndexes()`](https://w [source]
If the method is passed an array of index names that includes a non-existent index, the method errors without dropping any of the specified indexes. [source]
[Index Builds on Populated Collections](https://www.mongodb.com/docs/manual/core/index-creation/) for more information on the behavior of indexing operations in MongoDB. [source]
- Using the ordering of the `{ item: 1, type: 1 }` index,[`min()`](https://www.mongodb.com#mongodb-method-cursor.min) limits the query to the documents that are at or above the index key bound of`item` equal to`apple` and`type` equal to`jonagold` , as in the following:db.products.find().min( { item: 'apple', type: 'jonagold' } ).hint( { item: 1, type: 1 } ) The query returns the following documents: { "_id" : 3, "item" : "apple", "type" : "jonagold", "price" : Decimal128("1.29") } { "_id" : 4, "item" : "apple", "type" : "jonathan", "price" : Decimal128("1.29") } { "_id" : 5, "item" : "apple", [source]
dering of the index `{ price: 1 }` ,[`min()`](https://www.mongodb.com#mongodb-method-cursor.min) limits the query to the documents that are at or above the index key bound of`price` equal to`1.39` and[`max()`](https://www.mongodb.com/docs/manual/reference/method/cursor.max/#mongodb-method-cursor.max) limits the query to the documents that are below the index key bound of`price` equal to`1.99` :db.products.find().min( { price: Decimal128("1.39") } ).max( { price: Decimal128("1.99") } ).hint( { price: 1 } ) The query returns the following documents: { "_id" : 10, "item" : "orange", "type" : "nav [source]
The following table compares the index build behavior starting in MongoDB 7.1 with earlier versions. [source]
Each [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod/#mongodb-binary-bin.mongod) in the replica set or sharded cluster *must* have [featureCompatibilityVersion](https://www.mongodb.com/docs/manual/reference/command/setFeatureCompatibilityVersion/#std-label-set-fcv) set to at least `4.4` to start index builds simultaneously across replica set members. [source]
Index builds on a replica set or sharded cluster build simultaneously across all data-bearing replica set members. For sharded clusters, the index build occurs only on shards containing data for the collection being indexed. The primary requires a minimum number of data-bearing [`voting`](https://www.mongodb.com/docs/manual/reference/replica-configuration/#mongodb-rsconf-rsconf.members-n-.votes) members (i.e commit quorum), including itself, that must complete the build before marking the index as ready for use. See [Index Builds in Replicated Environments](https://www.mongodb.com/docs/manual/ [source]
`commitQuorum` specifies how many data-bearing voting members, or which voting members, including the primary, must be prepared to commit the index build before the primary will execute the commit. The default commit quorum is `votingMembers`, which means all data-bearing members. [source]
- `db.collection.createIndexes( [ keyPatterns ], options, commitQuorum )`- Creates one or more indexes on a collection. [source]
The single field index on the field `cat` has the user-specified name of `catIdx` and the index specification document of `{ "cat" : -1 }`. [source]
Or you can use the index specification document `{ "cat" : -1 }`: [source]
The `dropIndex` command returns the number of indexes in the collection prior to the command being run, and indicates whether the command was successful: [source]
[`db.collection.getIndexes()`](https://www.mongodb.com#mongodb-method-db.collection.getIndexes) returns an array of documents that hold index information for the collection. For example: [source]
For information on the keys and index options, see `db.collection.createIndex()`. [source]
- The amount of time a query took to complete - Whether the query used an index - The number of documents and index keys scanned to fulfill a query [source]
1. Click the Indexes tab for the `test.inventory` collection. 2. Click Create Index. 3. Select `quantity` from the Select a field name dropdown. 4. Select `1 (asc)` from the type dropdown. 5. Click Create. [source]
Only use a [rolling index build](https://www.mongodb.com/docs/manual/core/rolling-index-builds/#std-label-rolling-index-build) if your deployment matches one of the following cases: [source]
If your deployment does not meet this criteria, use the [default index build.](https://www.mongodb.com/docs/manual/core/index-creation/#std-label-index-operations) [source]
With Atlas, you can temporarily [scale](https://www.mongodb.com/docs/atlas/scale-cluster/) your cluster to meet the requirements for a traditional index build. However, Atlas charges to scale your cluster. See [Cluster Configuration Costs](https://www.mongodb.com/docs/atlas/billing/cluster-configuration-costs/) for more information. [source]
Rolling index builds lower the resiliency of your cluster and increase build duration. [source]
s/manual/reference/method/db.collection.dropIndex/#mongodb-method-db.collection.dropIndex) from a[`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos/#mongodb-binary-bin.mongos) to drop the index from the collection. [source]
For example, if you want to create an index on the `records` collection in the `test` database: [source]
Index searches make efficient use of exact matches to reduce the number of index keys examined. Equality fields must come first. [source]
const specification = { "accounts.bank": 1, "accounts.number": 1 }; const optionsV2 = { name: "Unique Account V2", partialFilterExpression: { "accounts.bank": { $exists: true }, "accounts.number": { $exists: true } }, unique: true }; db.users.drop( {} ); // Delete previous documents and indexes definitions db.users.createIndex(specification, optionsV2); // Unique Account V2 [source]
The `length` argument is specific to MySQL and allows you to define indexes and constraints on columns of `String` and `Byte` types. For these types, MySQL requires you to specify a maximum length for the subpart of the value to be indexed in cases where the full value would exceed MySQL's limits for index sizes. See [the MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html) for more details. [source]
* Not suitable for very short strings (less than 3 characters) * May produce false positives * Index size can be large for big text columns * Not ideal for exact matching (use standard indexes instead) [source]
▸ **withIndex**<`IndexName`>(`indexName`, `indexRange?`): [`Query`](/api/interfaces/server.Query.md)<`TableInfo`> [source]
- Use the [`$indexStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/#mongodb-pipeline-pipe.-indexStats) aggregation stage. - For MongoDB Atlas deployments, view [Indexes](https://www.mongodb.com/docs/atlas/atlas-ui/indexes/#std-label-atlas-ui-view-indexes) in the Atlas UI. [source]
n stage returns information about common[query shapes](https://www.mongodb.com/docs/manual/core/query-shapes/#std-label-query-shapes) .`$queryStats` provides a holistic view of the kinds of queries being run on your deployment. | | View index statistics | Atlas clusters and self-hosted deployments | The [`$indexStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/#mongodb-pipeline-pipe.-indexStats) aggregation stage returns information about your collection's indexes and how often individual indexes are used. Use`$indexStats` to identify unused indexes that can [source]
▸ **vectorSearch**<`TableName`, `IndexName`>(`tableName`, `indexName`, `query`): `Promise`<{ `_id`: [`GenericId`](/api/modules/values.md#genericid)<`TableName`> ; `_score`: `number` }\[]> [source]
| Name | Type | | ----------- | ---------------------------------------- | | `TableName` | extends `string` | | `IndexName` | extends `string` \| `number` \| `symbol` | [source]
| Name | Type | | ----------- | ---------------------------------------------------------------------------------- | | `TableInfo` | extends [`GenericTableInfo`](/api/modules/server.md#generictableinfo) | | `IndexName` | extends [`VectorIndexNames`](/api/modules/server.md#vectorindexnames)<`TableInfo`> | [source]
| Name | Type | | ---- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `q` | [`VectorFilterBuilder`](/api/interfaces/server.VectorFilterBuilder.md)<[`DocumentByInfo`](/api/modules/se [source]
| Name | Type | | ----------- | ---------------------------------------------------------------------- | | `TableInfo` | extends [`GenericTableInfo`](/api/modules/server.md#generictableinfo) | | `IndexName` | extends [`IndexNames`](/api/modules/server.md#indexnames)<`TableInfo`> | [source]
Ƭ **NamedSearchIndex**<`TableInfo`, `IndexName`>: [`SearchIndexes`](/api/modules/server.md#searchindexes)<`TableInfo`>\[`IndexName`] [source]
| Name | Type | | ----------- | ---------------------------------------------------------------------------------- | | `TableInfo` | extends [`GenericTableInfo`](/api/modules/server.md#generictableinfo) | | `IndexName` | extends [`SearchIndexNames`](/api/modules/server.md#searchindexnames)<`TableInfo`> | [source]
Ƭ **VectorSearch**<`DataModel`, `TableName`, `IndexName`>: (`tableName`: `TableName`, `indexName`: `IndexName`, `query`: [`VectorSearchQuery`](/api/interfaces/server.VectorSearchQuery.md)<[`NamedTableInfo`](/api/modules/server.md#namedtableinfo)<`DataModel`, `TableName`>, `IndexName`>) => `Promise`<{ `_id`: [`GenericId`](/api/modules/values.md#genericid)<`TableName`> ; `_score`: `number` }\[]> [source]
▸ (`tableName`, `indexName`, `query`): `Promise`<{ `_id`: [`GenericId`](/api/modules/values.md#genericid)<`TableName`> ; `_score`: `number` }\[]> [source]
| Name | Type | | ----------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `tableName` | `TableName` | | `indexName` | `IndexName` [source]
| Name | Type | | -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `runQuery` | (`name`: `string`, `args`?: `Record<string, Value>`) => `Promise<Value>` | | `runMutation` | [source]
- Run `db.collection.createIndex()` on the primary for a replica set - Run `db.collection.createIndex()` on the[`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos/#mongodb-binary-bin.mongos) for a sharded cluster [source]
The following example shows a `dropIndexes` event: [source]
| Field | Type | Description | |---|---|---| | `listIndexes` | string | The name of the collection. | | `cursor.batchSize` | integer | Optional. Specifies the cursor batch size. | | `comment` | any | Optional. A user-provided comment to attach to this command. Once set, this comment appears alongside records of this command in the following locations: [mongod log messages](https://www.mongodb.com/docs/manual/reference/log-messages/#std-label-log-messages-ref) , in the`attr.command.cursor.comment` field. [Database profiler](https://www.mongodb.com/docs/manual/reference/database-profiler/#std-la [source]
and) field. A comment can be any valid [BSON type](https://www.mongodb.com/docs/manual/reference/bson-types/#std-label-bson-types) (string, integer, object, array, etc). Any comment set on a `listIndexes` command is inherited by any subsequent[`getMore`](https://www.mongodb.com/docs/manual/reference/command/getMore/#mongodb-dbcommand-dbcmd.getMore) commands run on the`listIndexes` cursor. | [source]
1 db.runCommand ( 2 { 3 listIndexes: "contacts", cursor: { batchSize: 1 } 4 } 5 ) [source]
The [`createIndex()`](https://www.mongodb.com#mongodb-method-db.collection.createIndex) method has the following form: [source]
| Field | Description | |---|---| | reIndex | The name of the collection to reindex. | [source]
For the database command, see the [`reIndex`](https://www.mongodb.com/docs/manual/reference/command/reIndex/#mongodb-dbcommand-dbcmd.reIndex) command. [source]

Related concepts

index build — is a dependent of Index lifecycle and health
index definition — is a part of Index lifecycle and health
withIndex — is a instance of Index lifecycle and health; Convex index API
index overhead — is a problem of Index lifecycle and health
unused index — is a problem of Index lifecycle and health
@@index — is a instance of Index lifecycle and health; Prisma schema index attributes