Index types — researched

The kinds of index the sources describe and what each is for: B-tree, hash, GIN, GiST, BRIN, compound, unique, partial, multikey, wildcard, text, geospatial, TTL, hidden, expression and clustered indexes. Child pack of Indexing.

Definitions

GiST indexes are a versatile type of index that can handle complex data types, such as geometric shapes, full-text search, and network addresses. [They are implemented using a custom data structure optimized for searching large amounts of data1](https://www.postgresql.org/docs/current/gist.html). Here are some key points: [source]
SP-GiST (Spatial Generalized Search Tree) indexes are a versatile index type offered by PostgreSQL. They are designed for complex, non-rectangular data types and work especially well with geometrical and network-based data. Here are some key points: [source]
An expression index stores the result of an expression rather than a raw column value. Use expression indexes when queries frequently filter or sort by a computed value. [source]
The `where` argument allows you to define [partial indexes](https://www.postgresql.org/docs/current/indexes-partial.html) (also known as filtered indexes). A partial index only includes rows that match a specified condition, which reduces the index size and improves both write performance and query performance for the indexed subset of data. [source]
The SP-GiST index is a good choice for many different non-balanced data structures. If the query matches the partitioning rule, it can be very fast. [source]
Bounds intersection refers to the point where multiple bounds overlap. For example, given the bounds `[ [ 3, Infinity ] ]` and `[ [ -Infinity, 6 ] ]`, the intersection of the bounds results in `[ [ 3, 6 ] ]`. [source]

Structure and components

Compound indexes may contain **a single** [hashed index field.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-hashed/#std-label-index-type-hashed) [source]
1. **Plan Your Hierarchy**: Design your tree structure carefully before implementation 2. **Index Usage**: Create GiST indexes for better query performance: ```sql theme={null} CREATE INDEX path_idx ON categories USING GIST (path); ``` 3. **Validation**: Implement checks to maintain data integrity 4. **Path Length**: Keep paths reasonably short for better performance [source]
1. **What is a GIN Index?** * A GIN index stores a set of `(key, posting list)` pairs. * The posting list contains row IDs where the key occurs. * Multiple posting lists can share the same row ID since an item can have multiple keys. * [Each key value is stored only once, making GIN indexes compact when the same key appears multiple times](https://www.postgresql.org/docs/current/gin-intro.html). 2. **Use Cases for GIN Indexes:** * GIN indexes are ideal for data values with multiple components, like arrays. * [They efficiently handle queries that search for specific component values within comp [source]
A `UNIQUE` index enforces that no two rows contain the same combination of values in the indexed columns. NULL values are considered distinct from each other, so a `UNIQUE` index permits multiple rows with NULL in the indexed columns. [source]
Compound indexes can contain different types of sparse indexes. The combination of index types determines how the compound index matches documents. [source]
1. Create the `contacts` collection:db.contacts.insertMany( [ { name: "Evander Otylia", phone: "202-555-0193", address: [ 55.5, 42.3 ] }, { name: "Georgine Lestaw", phone: "714-555-0107", address: [ -74, 44.74 ] } ] ) The `address` field contains[legacy coordinate pairs.](https://www.mongodb.com/docs/manual/geospatial-queries/#std-label-geospatial-legacy) 2. To query for location data with the `$near` operator, you must create a[geospatial index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-geospatial/#std-label-geospatial-index) on the field that contains the location da [source]
With the compound index on `department` and `description`, the query only examines **one** index key. There is only one document in the collection where `department` is `kitchen` and the `description` contains the string `green`. [source]
- Create a wildcard index that only covers specific fields. For example, if you have multiple embedded documents with multiple subfields, you can create an index to cover queries on both embedded documents and their subfields. - Create a wildcard index that omits specific fields. For example, if you have a collection that contains a field that is never queried, you can omit that field from the index. [source]
| Parameter | Type | Description | |---|---|---| | `keys` | document | A document that contains the 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` . An asterisk (`*` ) is not a valid index name. MongoDB supports several different index types, including: See [index types](https://www.mongodb.com/docs/manual/core/indexes/index-types/#std-label-index-types) for more information. [Wildcard indexes](https://www.mongodb.com/docs/ma [source]
As part of the `JsonbPathOps` the `@>` operator is handled by the index, speeding up queries such as `value @> '{"foo": 2}'`. [source]
> This part of the MongoDB manual covers getting started, administration, and core concepts like clustered collections, change streams, and geospatial and other index types. [source]
The GIN index stores composite values, such as arrays or `JsonB` data. This is useful for speeding up querying whether one object is part of another object. It is commonly used for full-text searches. [source]
When you run `prisma db pull` on a database that contains partial indexes, Prisma ORM will: [source]
**1. Structure of B-Tree Indexes:** [source]
If your application repeatedly runs a query that contains multiple fields, you can create a compound index to improve performance for that query. For example, a grocery store manager often needs to look up inventory items by name and quantity to determine which items are low stock. You can create a compound index on both the `item` and `quantity` fields to improve query performance. [source]
A single compound index can contain up to 32 fields. [source]
The unique index allows the insertion of a document without the `email` field if the collection does not already contain a document missing the `email` field: [source]
When you create a geospatial index on a field that contains [legacy coordinate pairs](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-legacy-coordinate-pairs), MongoDB computes [geohash](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-geohash) values for the coordinate pairs within the specified [location range](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d/create/define-location-range/#std-label-2d-index-define-location-range), then indexes the geohash values. [source]
Because `test_scores` contains an array value, MongoDB stores this index as a multikey index. [source]
This index supports single-field queries on any field in the collection. If a document contains an embedded document or array, the wildcard index traverses the document or array and stores the value for all fields in the document or array. [source]
The following operation creates a wildcard index that contains all scalar values (meaning strings and numbers) of the `attributes.size` and `attributes.color` fields: [source]
MongoDB can enforce a uniqueness constraint on a ranged shard key index. Using a unique index on the shard key enforces uniqueness on the entire key combination and not individual components of the shard key. [source]

How it works

| Parameter | Type | Description | |---|---|---| | `unique` | boolean | Optional. Creates a unique index so that the collection will not accept insertion or update of documents where the index key value matches an existing value in the index. Specify `true` to create a unique index. The default value is`false` . The option is *unavailable* for[hashed indexes.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-hashed/#std-label-index-hashed-index) | | `name` | string | Optional. The name of the index. If unspecified, MongoDB generates an index name by concatenating the names of [source]
The fields `"ratings.scores.q1"` and `"ratings.scores.q2"` share the field path `"ratings.scores"`. In order to compound index bounds, a query must use `$elemMatch` on the common field path. [source]
Because the constraint applies to separate documents, for a unique [multikey](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/#std-label-index-type-multikey) index, a document may have array elements that result in repeating index key values as long as the index key values for that document do not duplicate those of another document. In this case, the repeated index entry is inserted into the index only once. [source]
1. Use hstore when dealing with dynamic attributes that don't require strict schema validation. 2. Consider using JSON/JSONB instead if you need to store nested structures or arrays. 3. Create indexes on frequently queried keys using GiST or GIN indexes: [source]
1. **Infrastructure**: SP-GiST indexes support various kinds of searches, similar to GiST indexes. [They permit the implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries) 1](https://www.postgresql.org/docs/current/spgist.html). 2. **Use Cases**: * **Geometric Searches**: SP-GiST is ideal for spatial data, such as points, lines, and polygons. * **IP Network Searches**: When dealing with IP addresses or network ranges. * [**Text Search with Complex Pattern Matching**: For scenarios where you need to search for pa [source]
Remember that BRIN indexes are most effective when dealing with large tables and specific column types. [source]
A composite index is useful when queries filter or sort by multiple columns. The order of columns matters -- an index on `(a, b)` can accelerate queries filtering on `a` alone, but not queries filtering only on `b`. [source]
The [btree\_gin](https://www.postgresql.org/docs/current/btree-gin.html) extension in PostgreSQL enables GIN indexes to support B-tree indexable data types. It is useful when you want to use a GIN index for multi-column queries that include standard B-tree searchable data types like `int`, `text`, `timestamp`, and `uuid`. Your Nile database arrives with `btree_gin` extension already enabled, so there's no need to run `create extension`. [source]
A GIN index with `btree_gin` is useful when performing multi-column searches that include B-tree indexable columns. Here's how to create one: [source]
- To utilize an index on an embedded document, your query must specify the entire embedded document. This can lead to unexpected behaviors if your schema model changes and you add or remove fields from your indexed document. - When you query embedded documents, the order that you specify fields in the query matters. The embedded documents in your query and returned document must match exactly. To see examples of queries on embedded documents, see [Query on Embedded/Nested Documents.](https://www.mongodb.com/docs/manual/tutorial/query-embedded-documents/#std-label-read-operations-subdocuments) [source]
- To use the `wildcardProjection` option, your index key must be`$**` . - Wildcard indexes don't support mixing inclusion and exclusion statements in the `wildcardProjection` document except when explicitly including the`_id` field. For example: - The following `wildcardProjection` document is**invalid** because it specifies both an inclusion and an exclusion of a field:{ "wildcardProjection" : { "attributes" : 0, "users" : 1 } } - The following `wildcardProjection` document is**valid** because even though it specifies both inclusion and exclusion, it includes the`_id` field:{ "wildcardProject [source]
* B-trees are versatile and widely applicable: * **Equality and Range Queries**: They excel in handling equality and range queries. Common operators include `=`, `<`, `>`, `BETWEEN`, and `IN`. * **NULL Conditions**: B-trees can handle `IS NULL` or `IS NOT NULL` conditions. * **Pattern Matching**: When anchored to the beginning of a string, they efficiently support pattern matching using `LIKE` or `~`. [source]
Previous versions of Prisma ORM used to create a *unique index* on these two columns. In Prisma v6, this unique index is changing to a *primary key* in order to [simplify for the default replica identity behaviour](https://github.com/prisma/orm/issues/25196). [source]
In MySQL, the default collation setting for string comparison is case-insensitive, which means that when performing operations like searching or comparing strings in SQL queries, the case of the characters does not affect the results. However, because collation settings can vary and may be configured to be case-sensitive, we will explicitly ensure that the `email` is unique regardless of case by creating a unique index on the lowercased `email` column. [source]
* Indexes that reference the column (including expression indexes) * Triggers that reference the column * CHECK constraints that reference the column * Foreign key constraint definitions [source]
- The values in the field you query with the `$geoWithin` operator must be in GeoJSON format. - When you specify longitude and latitude coordinates, list the **longitude** first, and then**latitude** . - Valid longitude values are between `-180` and`180` , both inclusive. - Valid latitude values are between `-90` and`90` , both inclusive. - When you specify Polygon `coordinates` , the first and last coordinates in the array must be the same. This closes the bounds of the polygon. - `$geoWithin` does not require a geospatial index. However, a geospatial index improves query performance. Only th [source]
- When you specify longitude and latitude coordinates, list the **longitude** first, and then**latitude** . - Valid longitude values are between `-180` and`180` , both inclusive. - Valid latitude values are between `-90` and`90` , both inclusive. - A location intersects with an object if it shares at least one point with the specified object. This includes objects that have a shared edge. - `$geoIntersects` does not require a geospatial index. However, a geospatial index improves query performance. Only the[2dsphere](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2dsph [source]
Compound bounds combine bounds for multiple keys of a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound). Using bounds from multiple keys reduces the time it takes to process a query because MongoDB does not need to compute results for each bound individually. [source]
If a field is an embedded document or array (like `attributes.size`), the wildcard index recurses into the field and indexes all embedded scalar field values. [source]
Unfortunately, it is expensive to create a lot of individual indexes to cover all of the possible queries. A wildcard index is a good alternative to creating a large number of individual indexes because one wildcard index can efficiently cover many potential queries. [source]
- If the field is an object, the wildcard index descends into the object and indexes its contents. The wildcard index continues descending into any additional embedded documents it encounters. - If the field is an array, the wildcard index traverses the array and indexes each element: - If the element is an object, the wildcard index descends into the object to index its contents. - If the element is an array (that is, an array which is embedded directly within the parent array), the wildcard index does not traverse the embedded array, but indexes the *entire* array as a single value. - For al [source]
When a wildcard index encounters an array, it traverses the array to index its elements. If the array element is itself an array (an embedded array), the index records the *entire* embedded array as a value instead of traversing its contents. [source]
The index records for `ship.coordinates` and `ship.captains` do not include the array position for each element. Wildcard indexes ignore array element positions when recording the element into the index. However, wildcard indexes can still support queries that include explicit array indices. [source]
If a field is a nested document or array, the wildcard index recurses into it and indexes all scalar fields in the document or array. [source]
If a field is a nested document or array, the wildcard index recurses into the document or array and indexes all scalar fields in the document or array. [source]
* All fields that make up the unique constraint **must** be mandatory fields. The following model is **not** valid because `id` could be `null`: [source]
The [citext](https://www.postgresql.org/docs/current/citext.html) extension in PostgreSQL provides a case-insensitive text type. It behaves just like the standard `TEXT` data type but treats values as case-insensitive when comparing or indexing, making it useful for case-insensitive searches and unique constraints. Your Nile database arrives with `citext` extension already enabled, so there's no need to run `create extension`. [source]
* Allows case-insensitive text comparisons without using `LOWER()`. * Simplifies case-insensitive unique constraints and indexes. * Reduces errors when working with user-provided text data like emails or usernames. [source]

Parameters and configuration

nual/core/indexes/index-types/#std-label-index-types) | | `sparse` | boolean | Optional. If `true` , the index only references documents with the specified field. These indexes use less space but behave differently in some situations (particularly sorts). The default value is`false` . See[Sparse Indexes](https://www.mongodb.com/docs/manual/core/index-sparse/) for more information. The following index types are sparse by default and ignore this option: For a compound index that includes `2dsphere` index keys and keys for other types, only the`2dsphere` index fields determine whether the index r [source]
| Name | Required | Type | Description | | ----------- | -------- | ---------------------------- | ------------------------------------------------------------------------------------------------------------------------------------ [source]
<Info> Note: `USING gin` specifies that this is a GIN index. The extension allows `column1` (text), `column2` (integer), and `column3` (timestamp) to be indexed efficiently using GIN. </Info> [source]
<Info> Note: `USING gist` specifies that this is a GiST index. The extension allows `column1` (text), `column2` (integer), and `column3` (timestamp) to be indexed efficiently using GiST. </Info> [source]

How-to and procedures

You can create multiple indexes on the same key(s) with different collations. To create indexes with the same key pattern but different collations, you must supply unique index names. [source]
You can also enforce a unique constraint on the combination of index key values for a [compound index.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound) [source]
To create a `hidden` index, use the [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#mongodb-method-db.collection.createIndex) method with the [hidden](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#std-label-method-createIndex-hidden) option set to `true`. [source]
Create a compound index on the `ratings.scores.q1` and the `ratings.scores.q2` fields: [source]
use inventory db.products_catalog.createIndexes( [ { "product_attributes.$**" : 1 } ] ) [source]
use inventory db.products_catalog.createIndexes( [ { "$**" : 1 } ] ) [source]
CREATE INDEX IF NOT EXISTS "title_search_index" ON "posts" USING gin (to_tsvector('english', "title")); ``` </CodeTab> ```json [ { id: 1, title: 'Planning Your First Trip to Europe' }, { id: 2, title: "Cultural Insights: Exploring Asia's Heritage" }, { id: 3, title: 'Top 5 Destinations for a Family Trip' }, { id: 4, title: 'Essential Hiking Gear for Mountain Enthusiasts' }, { id: 5, title: 'Trip Planning: Choosing Your Next Destination' }, { id: 6, title: 'Discovering Hidden Culinary Gems in Italy' }, { id: 7, title: 'The Ultimate Road Trip Guide for Explorers' }, ]; ``` </CodeTabs> [source]
CREATE INDEX IF NOT EXISTS "search_index" ON "posts" USING gin ((setweight(to_tsvector('english', "title"), 'A') || setweight(to_tsvector('english', "description"), 'B'))); ``` </CodeTab> ```json [ { id: 1, title: 'Planning Your First Trip to Europe', description: 'Get essential tips on budgeting, sightseeing, and cultural etiquette for your inaugural European adventure.', }, { id: 2, title: "Cultural Insights: Exploring Asia's Heritage", description: 'Dive deep into the rich history and traditions of Asia through immersive experiences and local interactions.', }, { id: 3, title: 'Top 5 Destin [source]
To create a wildcard index, use the wildcard specifier (`$**`) as the index key: [source]
To learn how to create a collection with a clustered index, see [Date Clustered Index Key Example.](https://www.mongodb.com/docs/manual/core/clustered-collections/#std-label-clustered-collections-index-example) [source]
To override the default version and specify a different version for your 2dsphere index, set the `2dsphereIndexVersion` option when you create an index: [source]
You can create an index on a field containing an array value to improve performance for queries on that field. When you create an index on a field containing an array value, MongoDB stores that index as a multikey index. [source]
To drop a `unique constraint`, the same syntax is used as dropping an index: `ALTER TABLE users DROP INDEX uq_email;`. There's no separate `DROP CONSTRAINT statement` for it in MySQL. [source]
To avoid this, use a partial filter expression so the index only includes documents that contain both fields. For more information, see [Partial Index with Unique Constraint](https://www.mongodb.com/docs/manual/core/index-partial/#std-label-partial-index-with-unique-constraints). Recreate the index using the following options: [source]
You can now query efficiently using operators like `@>`, and PostgreSQL will leverage this index. [source]
Run `collMod` on the `type` field index and set `prepareUnique` to `true`: [source]
To use partial indexes, add the `partialIndexes` feature flag to the `generator` block of your `schema.prisma` file: [source]
You can define a partial index with a raw SQL predicate string using the `raw()` function. This approach supports any valid SQL `WHERE` expression that your database accepts: [source]
You can also define partial indexes using an object literal syntax, which provides type-safety by validating field names and value types against your Prisma schema: [source]
To implement a unique and case-insensitive `email` handling in PostgreSQL with Drizzle, you can create a unique index on the lowercased `email` column. This way, you can ensure that the `email` is unique regardless of the case. [source]
To implement a unique and case-insensitive `email` handling in SQLite with Drizzle, you can create a unique index on the lowercased `email` column. This way, you can ensure that the `email` is unique regardless of the case. [source]
To implement an upsert query in MySQL with Drizzle you can use `.onDuplicateKeyUpdate()` method. MySQL will automatically determine the conflict target based on the primary key and unique indexes, and will update the row if any unique index conflicts. [source]
Function form of dot notation — `struct_extract(col, 'field')` is equivalent to `col.field`. Primarily useful in expression indexes, where dot notation cannot be used: [source]
To create a compound index, see [Create a Compound Index.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index/#std-label-index-create-compound) [source]
To hide an existing index, you can use the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collMod/#mongodb-dbcommand-dbcmd.collMod) command or [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#mongodb-binary-bin.mongosh) helper `db.collection.hideIndex()`. [source]
To unhide a hidden index, you can use the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collMod/#mongodb-dbcommand-dbcmd.collMod) command or [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#mongodb-binary-bin.mongosh) helper [`db.collection.unhideIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.unhideIndex/#mongodb-method-db.collection.unhideIndex). You can specify either: [source]
Create a unique compound [multikey](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/#std-label-index-type-multikey) index on `email` and `name`: [source]
To convert a non-unique index to a [unique index](https://www.mongodb.com/docs/manual/core/index-unique/#std-label-index-type-unique), use the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collMod/#mongodb-dbcommand-dbcmd.collMod) command. The `collMod` command provides options to verify that your indexed field contains unique values before you complete the conversion. [source]
To create a unique index on the `user_id` field of the `members` collection, run the following command in `mongosh`: [source]
Consider the following wildcard index on the `employees` collection: [source]
You can define the range of coordinates included in a [2d index](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d/#std-label-2d-index). By default, 2d indexes have longitude and latitude boundaries of: [source]
To change the location range of a 2d index, specify the `min` and `max` options when you create the index: [source]
Create a 2d index on the `address` field. Specify the following location bounds: [source]
You can use the 2d index to perform calculations on location data, such as [proximity queries.](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d/query/proximity-flat-surface/#std-label-2d-index-proximity-query) [source]
To index all of the coordinate pairs in the `locs` array, create a 2d index on the `locs` field: [source]
To index all of the `loc` values in the `addresses` array, create a 2d index on the `addresses.loc` field: [source]
Create a [compound multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound) on the `item` and `ratings` fields: [source]
Create a compound index on the `inventory` collection that contains the following fields: [source]
You can create a wildcard index that supports queries on all possible document fields. Wildcard indexes support queries on arbitrary or unknown field names. [source]
To include or exclude fields in a wildcard index, specify the chosen fields in the `wildcardProjection` option: [source]
To learn how to use wildcard projection with a compound wildcard index to filter fields, see [Filter Fields with a `wildcardProjection`.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-wildcard/index-wildcard-compound/#std-label-wc-compound-index-wcProject) [source]
Use a compound wildcard index instead. The compound wildcard index is easier to write, easier to maintain, and is unlikely to reach the 64 index collection limit. [source]
To create a [partial](https://www.mongodb.com/docs/manual/core/index-partial/#std-label-index-type-partial) compound wildcard index, you can use the `partialFilterExpression` option to specify a filter expression so that the index only includes documents that match the filter condition. `partialFilterExpression` can cover fields included or not included in the index. [source]
Create a wildcard index that includes the `ship` field: [source]
Consider the following wildcard index on a `books` collection: [source]
To support the query, add a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound). With compound indexes, the order of the fields matter. [source]
To create [unique indexes](https://www.mongodb.com/docs/manual/core/index-unique/#std-label-index-type-unique) using the following procedure, you must stop all writes to the collection during this procedure. [source]
To improve query performance, you can create a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound) that narrows the documents that queries read. For example, if you want to improve performance for queries on `status` and `product_type`, you could create a compound index on those two fields. [source]
To design your database so that it confines its documents to the application’s rules, combine a unique index and schema validation on your database using the following procedure. [source]
You can also create a named compound ID or compound unique constraint by using the `@@id` or `@@unique` attributes' `name` field. For example: [source]

Problems, failure modes and limitations

- If a hidden index is a [unique index](https://www.mongodb.com/docs/manual/core/index-unique/#std-label-index-type-unique) , the index still applies its unique constraint to the documents. - If a hidden index is a [TTL index](https://www.mongodb.com/docs/manual/core/index-ttl/#std-label-index-feature-ttl) , the index still expires documents. - Hidden indexes are included in [`listIndexes`](https://www.mongodb.com/docs/manual/reference/command/listIndexes/#mongodb-dbcommand-dbcmd.listIndexes) and[`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.g [source]
If a document has a `null` or missing value for the indexed field in a unique single-field index, the index stores a `null` value for that document. Because of the unique constraint, a single-field unique index can only contain one document that contains a `null` value in its index entry. If there is more than one document with a `null` value in its index entry, the index build fails with a duplicate key error. [source]
| Parameter | Description | | ------------------------- | ------------------------------------------------------------------------------------------------------------------------------- | | `UNIQUE` | Enforces a uniqueness constraint. Turso rejects any INSERT or UPDATE that would create duplicate values in the indexed columns. | | `IF NOT EXISTS` | Prevents an error if an index with the same name already exists. The statement is a no-op [source]
* GIN indexes can be large, plan storage accordingly * Index creation might be slow for large tables * Index only necessary columns * Monitor index size and search performance * Adjust similarity threshold to balance precision and recall * Consider using `pg_bigm.enable_recheck` for better accuracy [source]
* GIN indexes provide faster search but slower updates * Index size can be large for text columns with many unique values * Consider partial indexes for large tables * Monitor and adjust similarity threshold based on false positive/negative rates [source]
| Condition | Error | | --------------------------------------------------------------------------------------- | ------------------------------------------------ | | The column is a PRIMARY KEY or part of one | Cannot drop PRIMARY KEY column | | The column has a UNIQUE constraint | Cannot drop UNIQUE column | | The column is referenced b [source]
After `prepareUnique` is set, you cannot insert new documents that duplicate an index key entry. For example, the following insert operation results in an error: [source]
* Faster searches * Slower updates * Larger index size * Better for static data [source]
* Enforced by a [compound index in MongoDB](https://www.mongodb.com/docs/manual/core/index-compound/) * A `@@unique` block cannot be used as the only unique identifier for a model - MongoDB requires an `@id` field [source]
* Only explicitly created indexes can be dropped. Indexes automatically created for PRIMARY KEY and UNIQUE constraints cannot be dropped directly -- drop the table or use ALTER TABLE instead. * Dropping an index may degrade query performance for queries that relied on it, but does not affect correctness. [source]
GIN and BTree are the only index types supported by CockroachDB. The operator classes marked to work with CockroachDB are the only ones allowed on that database and supported by Prisma ORM. The operator class cannot be defined in the Prisma Schema Language: the `ops` argument is not necessary or allowed on CockroachDB. [source]
- For a to-be-sharded collection, you cannot shard the collection if the collection has multiple unique indexes unless the shard key is the prefix for all the unique indexes. - For an already-sharded collection, you cannot create unique indexes on other fields unless the shard key is included as the prefix. - A unique index stores a null value for a document missing the indexed field; that is a missing index field is treated as another instance of a `null` index key value. For more information, see[Missing Document Field in a Unique Single-Field Index.](https://www.mongodb.com#std-label-unique [source]
If the query does not join the conditions on the array field with `$elemMatch`, MongoDB cannot intersect the multikey index bounds. [source]
You cannot [drop](https://www.mongodb.com/docs/manual/reference/method/db.collection.dropIndex/#std-label-collection-drop-index) or [hide](https://www.mongodb.com/docs/manual/reference/method/db.collection.hideIndex/#std-label-collection-hide-index) an index if it is the only non-hidden index that supports the shard key. [source]
If you cannot stop all writes to the collection during this procedure, do not use the procedure on this page. Instead, build your unique index on the collection by issuing [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#mongodb-method-db.collection.createIndex) on the primary for a replica set. [source]
If you cannot stop all writes to the collection during this procedure, do not use the procedure on this page. Instead, build your unique index on the collection by issuing [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex/#mongodb-method-db.collection.createIndex) on the [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos/#mongodb-binary-bin.mongos) for a sharded cluster. [source]
When you insert `user1` without the `accounts.bank` and `accounts.number` fields, MongoDB sets them to `null` and adds a unique index entry. Any later insert that also lacks either field, such as `user2`, causes a duplicate key error. [source]
Note that in PostgreSQL, while you cannot specify sort order on unique constraints directly, you can create a unique index with a sort order that will enforce uniqueness: [source]
MongoDB cannot create a [unique index](https://www.mongodb.com#std-label-index-type-unique) on the specified index field(s) if the collection already contains data that would violate the unique constraint for the index. [source]
You cannot specify a unique constraint on a [hashed index.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-hashed/#std-label-index-type-hashed) [source]
> [!WARNING] > Indexes using a function (such as `to_tsvector`) to determine the indexed value are not yet supported by Prisma ORM. Indexes defined in this way will not be visible with `prisma db pull`. [source]
> [!NOTE] > The object literal syntax validates field types. For example, you cannot use a `Boolean` value for a `String` field. For fields with types that are not supported by the object syntax (such as `Unsupported` or composite types), use `raw()` instead. [source]
MongoServerError: E11000 duplicate key error collection: test.apples index: type_1 dup key: { type: "Delicious" } [source]
The pattern `"$**"` includes all fields in the document. Use the `wildcardProjection` field to limit the index to fields you specify. For complete documentation on `wildcardProjection`, see [Options for `wildcard` indexes.](https://www.mongodb.com#std-label-createIndex-method-wildcard-option) [source]
SQL Server [allows only one `NULL` value per `UNIQUE` constraint](https://learn.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints). Use filtered indexes to work around this, but note they cannot be used as foreign keys. [source]
The `Hash` type will store the index data in a format that is much faster to search and insert, and that will use less disk space. However, only the `=` and `<>` comparisons can use the index, so other comparison operators such as `<` and `>` will be much slower with `Hash` than when using the default `BTree` type. [source]
> [!WARNING] > **CockroachDB limitation**: CockroachDB supports creating partial indexes, but it cannot introspect the predicate text from existing indexes. This means that after initial creation, modifications to the `where` clause (adding, changing, or removing a predicate) will not be detected by Prisma Migrate. The differ skips predicate comparison for CockroachDB to prevent false-positive migrations. [source]
<Callout type="warning"> Functional indexes are supported in MySQL starting from version `8.0.13`. For the correct syntax, the expression should be enclosed in parentheses, for example, `(lower(column))`. </Callout> [source]
* `CITEXT` is slightly **slower** than `TEXT` due to case normalization. * It does **not support LIKE queries** efficiently unless you create a functional index using `LOWER(column1)`. * Collation-sensitive operations may not always behave as expected. [source]
For replica sets and sharded clusters, using a [rolling procedure](https://www.mongodb.com/docs/manual/tutorial/build-indexes-on-replica-sets/#std-label-index-build-on-replica-sets) to create a unique index requires that you stop all writes to the collection during the procedure. If you cannot stop all writes to the collection during the procedure, do not use the rolling procedure. Instead, to build your unique index on the collection you must either: [source]
Using a 2d index for queries on spherical data can return incorrect results or an error. For example, 2d indexes don't support spherical queries that wrap around the poles. [source]
You cannot create a 2d index if your collection contains coordinate data outside of the index's location range. [source]
After you create a 2d index, you cannot insert a document that contains coordinate data outside of the index's location range. [source]

Comparisons and alternatives

3. **Performance Considerations:** * GIN indexes are efficient for array-based queries but may have overhead during updates. * Consider the trade-off between query performance and update cost. * Regularly vacuum the GIN index to maintain performance. [source]
* GIN indexes are optimized for **fast lookups** but have **slower insert/update performance** compared to B-tree indexes. * They work best when querying multiple indexed columns together. * Unlike B-tree indexes, they **do not support range queries efficiently**. [source]
* GiST indexes generally **do not provide the same performance as B-tree indexes** for single-column lookups. * They **excel at multi-column queries** and range searches but can be **slower for simple equality lookups**. * `btree_gist` is useful primarily for **exclusion constraints** rather than improving query performance. [source]
1. **Structure**: * Hash indexes store only the hash value of the data being indexed. * No restrictions on the size of the indexed column. * Support only single-column indexes. * Do not allow uniqueness checking. 2. **Use Cases**: * Ideal for scenarios where exact matches are common. * Not suitable for range queries or pattern matching. 3. **Performance Considerations**: * Fast for equality lookups. * Minimal overhead during data insertion. * Not automatically maintained (unlike B-tree indexes). [source]
* GiST indexes are powerful but may have higher insertion and maintenance costs compared to B-tree indexes. * Choose the appropriate operator class and indexing strategy based on your data type and query requirements. [source]
* `@id` marks the primary key; `@@id([a, b])` declares a composite key. * `@unique` adds a unique constraint; `@@index([...])` declares a secondary index. * `@default(...)` sets a default. Database function defaults such as `@default(now())` become column defaults in the database. Generated defaults such as `@default(uuid())` are applied by Prisma 8 before each write instead, so they work the same on every database. They appear in the contract's `execution` section rather than as DDL. * `@map("column_name")` sets a field's physical name; `@@map("table_name")` sets the table or collection name [source]
* Bloom filters are probabilistic and can produce **false positives**, meaning they may return more results than expected. * They are best suited for queries filtering multiple indexed columns using **equality conditions** (`=`). * Unlike B-tree indexes, they **do not support range queries** (`<`, `>`, `BETWEEN`). [source]
* Indexes can be larger compared to traditional B-tree indexes * Not suitable for exact matching (use standard indexes instead) * May require more memory during search operations * Performance depends on similarity threshold and data size [source]
If your data is stored as longitude and latitude and you often run queries on spherical surfaces, use a [2dsphere index](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2dsphere/#std-label-2dsphere-index) instead of a 2d index. [source]

Facts and statements

* The [`type` argument](#configuring-the-access-type-of-indexes-with-type-postgresql) allows you to support index access methods other than PostgreSQL's default `BTree` access method * Available on the `@@index` attribute * PostgreSQL only * Supported index access methods: `Hash`, `Gist`, `Gin`, `SpGist` and `Brin` [source]
The `type` argument is available for configuring the index type in PostgreSQL with the `@@index` attribute. The index access methods available are `Hash`, `Gist`, `Gin`, `SpGist` and `Brin`, as well as the default `BTree` index access method. [source]
ound index that includes `2dsphere` index keys and keys for other types, only the`2dsphere` index fields determine whether the index references a document. [Partial indexes](https://www.mongodb.com/docs/manual/core/index-partial/#std-label-index-type-partial) have a superset of the sparse index functionality. Unless your application has a specific requirement, use partial indexes instead of sparse indexes. | | `expireAfterSeconds` | integer | Optional. Specifies a value, in seconds, as a time to live ([TTL](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-TTL) ) to control how [source]
| Index type (Algorithm) | Supported | Prisma schema | Prisma Client | Prisma Migrate | | ---------------------- | :-------: | :-----------: | :-----------: | :------------: | | B-tree | ✔️ | ✔️† | ✔️ | Not yet | | Hash | ✔️ | ✔️† | ✔️ | Not yet | | GiST | ✔️\* | ✔️† | ✔️\* | Not yet | | GIN | ✔️\* | ✔️† | ✔️\* | Not yet | | BRIN | ✔️\* | ✔️† | ✔️\* | [source]
| Parameter | Type | Description | |---|---|---| | `unique` | boolean | Optional. Specifies that each index specified in the `keyPatterns` array is a[unique index](https://www.mongodb.com/docs/manual/core/index-unique/#std-label-index-type-unique) . Unique indexes will not accept insertion or update of documents where the index key value matches an existing value in the index. Specify `true` to create a unique index. The default value is`false` . The option is *unavailable* for[hashed indexes.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-hashed/#std-label-index-hashed-in [source]
| Column | Type | Description | | ------- | ------- | --------------------------------------------------------------------- | | seq | INTEGER | Index sequence number | | name | TEXT | Index name | | unique | INTEGER | 1 if the index is UNIQUE | | origin | TEXT | `c` for CREATE INDEX, `u` for UNIQUE constraint, `pk` for PRIMARY KEY | | partial | INTEGER | 1 if the index [source]
```sql theme={null} CREATE INDEX idx_location ON employees USING GIST (location); [source]
* To create a GIN index on the `skills` column: ```sql theme={null} CREATE INDEX idx_gin_skills ON employees USING gin(skills); ``` [source]
This example creates multiple indexes with the same key pattern and different `sparse` options: [source]
Let's create an example `employees` table and demonstrate Hash index usage: [source]
Let's create an employee table and demonstrate BRIN index usage. [source]
The unique index permits the insertion of the following document into the collection if no other document in the collection has an index key value of `{ "email": "[email protected]", "name": null }`. [source]
* **Length configuration** for MySQL indexes * **Sort order** configuration for indexes * **Additional index types** for PostgreSQL: * Hash * GIN * GiST * SP-GiST * BRIN * **Index clustering** for SQL Server [source]
A unique index ensures that the indexed fields do not store duplicate values, and that a value appears at most once for a given field. A unique compound index ensures that any given combination of the index key values appears at most once. By default, MongoDB creates a unique index on the [_id](https://www.mongodb.com/docs/manual/core/document/#std-label-document-id-field) field during the creation of a collection. [source]
Basic and unique indexes can exist with the same [key pattern.](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndexes/#std-label-key_patterns) [source]
- To learn how to create a multikey index on embedded document fields, see [Create an Index on an Embedded Field in an Array.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-embedded/#std-label-index-create-multikey-embedded) - To learn about multikey index bounds, see [Multikey Index Bounds.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds/#std-label-indexes-multikey-bounds) [source]
- Compound wildcard indexes are sparse indexes. - Documents are included in the index if they are missing the wildcard field but have one of the compound fields. - Index fields, including wildcard fields, can be sorted in ascending ( `1` ) or descending (`-1` ) order. [source]
he following form: The `<value>` can be either of the following: `1` or`true` to include the field in the wildcard index. `0` or`false` to exclude the field from the wildcard index. Wildcard indexes omit the `_id` field by default. To include the`_id` field in the wildcard index, you must explicitly include it in the`wildcardProjection` document: All of the statements in the `wildcardProjection` document must be either inclusion or exclusion statements. You can also include the`_id` field with exclusion statements. This is the only exception to the rule. Options specified to [`db.collection.cr [source]
* Corresponding database construct: `UNIQUE` * A `@@unique` block is required if it represents the only unique constraint on a model without an `@id` / `@@id` * Adding a unique constraint automatically adds a corresponding *unique index* to the specified column(s) [source]
> **Note**: The `where` argument accepts either `raw("SQL expression")` for raw SQL predicates or an object literal like `{ field: value }` for type-safe conditions. See [Configuring partial indexes](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/indexes#configuring-partial-indexes-with-where) for details. [source]
| Name | Required | Type | Description | | ----------- | -------- | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ [source]
The `where` argument is available on the `@unique`, `@@unique` and `@@index` attributes. It requires the `partialIndexes` Preview feature. [source]
The default operator class (marked with ✅) can be omitted from the index definition. [source]
Queries like `value = 2` will now use the index, which uses a fraction of the space used by the `BTree` or `Hash` indexes. [source]
A GiST index with `btree_gist` can be used for multi-column searches and exclusion constraints. Here's how to create one: [source]
* gin must be used as an index method. GiST is not available for pg\_bigm. * gin\_bigm\_ops must be used as an operator class. [source]
Hash indexes use a hash function to map indexed column values to 32-bit hash codes. These indexes are optimized for simple equality comparisons (using the `=` operator). Here's how they work: [source]
* **Purpose**: GiST indexes are designed to support various query types, including equality queries, range queries, and partial match queries. * **Infrastructure**: GiST provides an infrastructure within which different indexing strategies can be implemented. * [**Operator Classes**: The operators used with GiST indexes depend on the specific indexing strategy (operator class) chosen2](https://www.postgresql.org/docs/current/indexes-types.html). [source]
Let's explore some examples using tables related to employees. We'll create a sample table, insert data, and demonstrate how GiST indexes work. [source]
Suppose we have an `employees` table with a `location` column representing the employees' office locations (stored as geometric points). We want to efficiently query employees based on their proximity to a specific location. [source]
3. **Create GiST Index**: [source]
Let's create an example employee table and demonstrate how to use SP-GiST indexes. [source]
A partial index includes only the rows that satisfy the `WHERE` clause. Partial indexes are smaller than full indexes and are more efficient for queries that always include the same filter condition. [source]
The `WHERE` clause of a partial index can reference any column of the table and may use operators, literal values, and built-in functions. Subqueries are not allowed. [source]
Each expression in the index must be a deterministic expression that references only columns of the indexed table. Aggregate functions and subqueries are not allowed. [source]
<Info> **Turso Extension**: Custom index methods extend indexing beyond B-trees. This feature is experimental and must be [enabled before use](/sql-reference/experimental-features). </Info> [source]
The following example creates a compound index on the `year`, `runtime`, and `title` fields: [source]
The following example creates a compound index on the `title` field (in ascending order) and the `runtime` field (hashed): [source]
* Balanced performance between search and update * Smaller index size * Good for dynamic data [source]
<Note> The `libsql_vector_idx` marker function is **required** and used by libSQL to distinguish `ANN`-indices from ordinary B-Tree indices. </Note> </Step> [source]
If you have a collection that has both a compound index and an index on its prefix (for example, `{ a: 1, b: 1 }` and `{ a: 1 }`), if neither index has a [sparse](https://www.mongodb.com/docs/manual/core/index-sparse/#std-label-index-type-sparse) or [unique](https://www.mongodb.com/docs/manual/core/index-unique/#std-label-index-type-unique) constraint, you can remove the index on the prefix (`{ a: 1 }`). MongoDB uses the compound index in all of the situations that it would have used the prefix index. [source]
> **Note**: With the `partialIndexes` Preview feature, the `where` argument is available. Before this Preview feature, the signature was: > > ```prisma no-lines > @@index(_ fields: FieldReference[], map: String?) > ``` [source]
> [!NOTE] > Partial indexes are now supported in Prisma Schema Language via the `where` argument on `@@index`, `@@unique`, and `@unique`. See [Configuring partial indexes](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/indexes#configuring-partial-indexes-with-where) for details. You no longer need to customize migrations for partial indexes. > > The Prisma schema is also able to represent [unsupported field types](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/unsupported-database-features#unsupported-field-types) and [native database functions](https://www.prisma.io/do [source]
The `clustered` argument is available to configure (non)clustered indexes in SQL Server. It can be used on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes. [source]
> [!NOTE] > Partial indexes are now supported in Prisma Schema Language via the `where` argument on `@@index`, `@@unique`, and `@unique`. See [Configuring partial indexes](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/indexes#configuring-partial-indexes-with-where) for details. [source]
| Entity | Convention | Example | | ----------------- | ------------------------------------ | ------------------------------ | | Primary Key | {tablename}_pkey | `User_pkey` | | Unique Constraint | {tablename}_{column_names}_key | `User_firstName_last_Name_key` | | Non-Unique Index | {tablename}_{column_names}_idx | `User_age_idx` | | Foreign Key | {tablename}_{column_names}_fkey | `User_childName_fkey` | [source]
The unique index prevents different documents in the collection from having the same value for the indexed key. [source]
For example, create a unique compound multikey index on `email` and `name`: [source]
The GiST index type is used for implementing indexing schemes for user-defined types. By default there are not many direct uses for GiST indexes, but for example the B-Tree index type is built using a GiST index. [source]
export const stores = pgTable( 'stores', { id: serial('id').primaryKey(), name: text('name').notNull(), location: geometry('location', { type: 'point', mode: 'xy', srid: 4326 }).notNull(), }, (t) => [ index('spatial_index').using('gist', t.location), ] ); ``` </CodeTab> ```sql CREATE TABLE IF NOT EXISTS "stores" ( "id" serial PRIMARY KEY NOT NULL, "name" text NOT NULL, "location" geometry(point) NOT NULL ); --> statement-breakpoint CREATE INDEX IF NOT EXISTS "spatial_index" ON "stores" USING gist ("location"); ``` </CodeTabs> [source]
// custom lower function export function lower(email: AnyPgColumn): SQL { return sql`lower(${email})`; } ``` </CodeTab> ```sql CREATE TABLE IF NOT EXISTS "users" ( "id" serial PRIMARY KEY NOT NULL, "name" text NOT NULL, "email" text NOT NULL ); --> statement-breakpoint CREATE UNIQUE INDEX IF NOT EXISTS "emailUniqueIndex" ON "users" USING btree (lower("email")); ``` </CodeTabs> [source]
Enables GIN indexes to support B-tree indexable data types. [source]
The `btree_gin` extension enhances GIN indexes by allowing them to handle B-tree indexable data types efficiently. It is particularly useful for **multi-column indexing** scenarios where a mix of text, integer, and timestamp fields are queried together. [source]
Allows GiST indexes to support B-tree indexable data types. [source]
The [btree\_gist](https://www.postgresql.org/docs/current/btree-gist.html) extension in PostgreSQL allows GiST indexes to support B-tree indexable data types. It is useful for indexing columns that typically use B-tree indexes but require additional GiST-specific features such as **multicolumn indexing**, **range queries**, and **support for exclusion constraints**. Your Nile database arrives with `btree_gist` extension already enabled, so there's no need to run `create extension`. [source]
The `btree_gist` extension enhances GiST indexes by allowing them to handle B-tree indexable data types efficiently. It is particularly useful for **multi-column indexing**, **range queries**, and **exclusion constraints**. [source]
* GIN indexes are typically better for exact matches and contained-by queries * GiST indexes are better for overlap queries but may be less precise * Array operations are performed in memory, so be cautious with very large arrays * Sorting and uniqueness operations (`sort`, `uniq`) create new arrays [source]
<Card title="Btree_Gin" href="./btree_gin" icon="martini-glass-citrus"> GIN index support for B-tree indexable data types. </Card> [source]
<Card title="Btree_Gist" href="./btree_gist" icon="tree"> GiST index support for B-tree indexable data types. </Card> [source]
1. **Index Selection**: * Use GIN for mostly-read data * Use GiST for frequently updated data * Consider creating indexes only on frequently searched columns [source]
1. **Indexing**: Use GIN or BTREE indexes for frequent lookups by `tenant_id` and specific keys. 2. **Normalization**: Use `jsonb` for semi-structured data. For structured data, use regular columns and normalize the schema. 3. **Use UUID for `tenant_id`**: Ensure the `tenant_id` is a UUID to uniquely identify tenants, providing clear data isolation. [source]
Compound indexes collect and sort data from multiple field values from each document in a collection. You can use the compound index to query the first field or any prefix fields of the index. The order of fields in a compound index is very important. The B-tree created by a compound index stores the sorted data in the order that the index specifies the fields. [source]
- the index key specification document to the [`db.collection.hideIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.hideIndex/#mongodb-method-db.collection.hideIndex) method:db.restaurants.hideIndex( { borough: 1, ratings: 1 } ); // Specify the index key specification document - the index name to the [`db.collection.hideIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.hideIndex/#mongodb-method-db.collection.hideIndex) method:db.restaurants.hideIndex( "borough_1_ratings_1" ); // Specify the index name [source]
- the index key specification document to the [`db.collection.unhideIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.unhideIndex/#mongodb-method-db.collection.unhideIndex) method:db.restaurants.unhideIndex( { borough: 1, city: 1 } ); // Specify the index key specification document - the index name to the [`db.collection.unhideIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.unhideIndex/#mongodb-method-db.collection.unhideIndex) method:db.restaurants.unhideIndex( "borough_1_ratings_1" ); // Specify the index name [source]
Given an indexed array field, consider a query that specifies multiple query predicates on the array and uses a [multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/#std-label-index-type-multikey) to fulfill the query. MongoDB can intersect the multikey index bounds if an [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch/#mongodb-query-op.-elemMatch) operator joins the query predicates. [source]
MongoDB supports creating wildcard indexes on a field or a set of fields. A compound index has multiple index terms. A compound wildcard index has one wildcard term and one or more additional index terms. [source]
The index key pattern is `"$**"`. You can create another wildcard index with the same key pattern if you specify a different `wildcardProjection`. For example: [source]
Although you can have a unique [compound index](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-compound-index) where the shard key is a [prefix](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-compound-index-prefix), if using `unique` parameter, the collection must have a unique index that is on the shard key. [source]
If your application performs queries on both a single key and multiple keys, a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound) is more efficient than a single-key index. For example, you can create an index on the `year`, `runtime`, and `title` fields: [source]
- `listIndexes`- Returns information about the indexes on the specified collection, including [hidden indexes](https://www.mongodb.com/docs/manual/core/index-hidden/#std-label-index-type-hidden) and indexes that are currently being built. Returned index information includes the keys and options used to create the index. You can optionally set the batch size for the first batch of results.## TipIn [`mongosh`](https://www.mongodb.com/docs/mongodb-shell/#std-program-mongosh) , this command can also be run through the[`db.collection.getIndexes()`](https://www.mongodb.com/docs/manual/reference/meth [source]
If the options specification had been split into multiple documents like this: `{ unique: true }, { sparse: true, expireAfterSeconds: 3600 }` the index creation operation would have failed. [source]
If the `keys` document specifies more than one field, then [`createIndex()`](https://www.mongodb.com#mongodb-method-db.collection.createIndex) creates a [compound index.](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-compound-index) [source]
ust be unique, you may only specify name if you are creating a single index using`db.collection.createIndexes()` . | | `partialFilterExpression` | document | Optional. If specified, the indexes only reference documents that match the filter expression. See [Partial Indexes](https://www.mongodb.com/docs/manual/core/index-partial/#std-label-index-type-partial) for more information. A filter expression can include: equality expressions (i.e. `field: value` or using the[`$eq`](https://www.mongodb.com/docs/manual/reference/operator/query/eq/#mongodb-query-op.-eq) operator) [`$exists: true`](https:/ [source]
- `db.collection.getIndexes()`- Returns an array that holds a list of documents that identify and describe the existing indexes on the collection, including [hidden indexes](https://www.mongodb.com/docs/manual/core/index-hidden/#std-label-index-type-hidden) and indexes that are currently being built.You must call [`db.collection.getIndexes()`](https://www.mongodb.com#mongodb-method-db.collection.getIndexes) on a collection. For example:db.collection.getIndexes() Change `collection` to the name of the collection for which to return index information. [source]
**3. Practical Examples:** Let's create an example `employees` table and demonstrate B-tree index usage: [source]
](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/#mongodb-pipeline-pipe.-indexStats) - Hiding an unhidden index or unhiding a hidden index resets its [`$indexStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/#mongodb-pipeline-pipe.-indexStats) . Hiding an already hidden index or unhiding an already unhidden index does not reset the[`$indexStats`.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/#mongodb-pipeline-pipe.-indexStats) [source]
* Corresponding database construct: `UNIQUE` * `NULL` values are considered to be distinct (multiple rows with `NULL` values in the same column are allowed) * Adding a unique constraint automatically adds a corresponding *unique index* to the specified column(s). [source]
That is why Drizzle always treats any `.unique()` as `UNIQUE INDEX` [source]
If you use both the `partialFilterExpression` and a unique constraint, the unique constraint only applies to documents that meet the filter expression. For an example, see [Partial Index with Unique Constraint.](https://www.mongodb.com/docs/manual/core/index-partial/#std-label-partial-index-with-unique-constraints) [source]
An indexed field can define the operator class, which defines the operators handled by the index. [source]
Prisma ORM generally supports operator classes provided by PostgreSQL in versions 10 and later. If the operator class requires the field type to be of a type Prisma ORM does not yet support, using the `raw` function with a string input allows you to use these operator classes without validation. [source]
| Operator class | Allowed field type (native types) | Default | Other | | -------------- | --------------------------------- | ------- | ----------------------------- | | `ArrayOps` | Any array | ✅ | Also available in CockroachDB | | `JsonbOps` | `Json` (`@db.JsonB`) | ✅ | Also available in CockroachDB | | `JsonbPathOps` | `Json` (`@db.JsonB`) | | | | `raw("other")` | | | | [source]
Read more about built-in operator classes in the [official PostgreSQL documentation](https://www.postgresql.org/docs/14/gin-builtin-opclasses.html). [source]
Queries comparing IP addresses, such as `value > '10.0.0.2'`, will use the index. [source]
| Operator class | Allowed field type (allowed native types) | | -------------- | ----------------------------------------- | | `InetOps` | `String` (`@db.Inet`) | | `raw("other")` | | [source]
Read more about built-in operator classes in the [official PostgreSQL documentation](https://www.postgresql.org/docs/14/gist-builtin-opclasses.html). [source]
As with GiST, SP-GiST is important as a building block for user-defined types, allowing implementation of custom search operators directly with the database. [source]
As an example, the following model adds a `SpGist` index to the `value` field with `TextOps` as the operators using the index: [source]
Queries such as `value LIKE 'something%'` will be sped up by the index. [source]
| Operator class | Allowed field type (native types) | Default | Supported PostgreSQL versions | | -------------- | ------------------------------------ | ------- | ----------------------------- | | `InetOps` | `String` (`@db.Inet`) | ✅ | 10+ | | `TextOps` | `String` (`@db.Text`, `@db.VarChar`) | ✅ | | | `raw("other")` | | | | [source]
Read more about built-in operator classes from [official PostgreSQL documentation](https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html). [source]
Prisma ORM generally supports operator classes provided by PostgreSQL in versions 10 and later, and some supported operators are only available from PostgreSQL versions 14 and later. If the operator class requires the field type to be of a type Prisma ORM does not yet support, using the `raw` function with a string input allows you to use these operator classes without validation. [source]
| Operator class | Allowed field type (native types) | Default | Supported PostgreSQL versions | | --------------------------- | ------------------------------------ | ------- | ----------------------------- | | `BitMinMaxOps` | `String` (`@db.Bit`) | ✅ | | | `VarBitMinMaxOps` | `String` (`@db.VarBit`) | ✅ | | | `BpcharBloomOps` | `String` (`@db.Char`) | | 14+ | | `BpcharMinMaxOps` | `St [source]
Read more about built-in operator classes in the [official PostgreSQL documentation](https://www.postgresql.org/docs/14/brin-builtin-opclasses.html). [source]
| Value type | Example | Notes | | ---------------- | ---------------------------------------- | ------------------------------------------------------ | | `Boolean` | `{ active: true }`, `{ deleted: false }` | For `Boolean` fields | | `String` | `{ status: "active" }` | For `String`, `DateTime`, and `Enum` fields | | `Number` | `{ priority: 1 }`, `{ score: 1.5 }` | For `Int`, `BigInt`, `Float`, and `Decimal` fields [source]
The `where` argument can be combined with other index arguments such as `name` and `map`: [source]
> [!NOTE] > The introspected `raw()` string reflects the database's normalized form of the SQL expression, which may differ from what you originally wrote. For example, PostgreSQL adds parentheses and explicit type casts (e.g., `'active'::text`), SQL Server wraps column names in brackets and adds parentheses (e.g., `([status]='active')`), while SQLite generally preserves the original expression as-is. [source]
This index allows fast searches for keys and values within `jsonb` fields for tenants: [source]
If you frequently query certain document fields, you can specify those fields in a `wildcardProjection` to support those queries without adding unnecessary bloat to the index. [source]
The `map` argument can also be used on unique constraints: [source]
export const user = cockroachTable('user', { id: int4().unique(), }); [source]
export const table = cockroachTable('table', { id: int4().unique('custom_name'), }); [source]
export const composite = cockroachTable('composite_example', { id: int4(), name: string(), }, (t) => [ unique().on(t.id, t.name), unique('custom_name').on(t.id, t.name) ]); [source]
export const user = mssqlTable('user', { id: int().unique(), }); [source]
export const user = mysqlTable('user', { id: int().unique(), }); [source]
export const user = pgTable('user', { id: integer().unique(), }); [source]
export const table = pgTable('table', { id: integer().unique('custom_name'), }); [source]
export const composite = pgTable('composite_example', { id: integer(), name: text(), }, (t) => [ unique().on(t.id, t.name), unique('custom_name').on(t.id, t.name) ]); [source]
// In Postgres 15.0+ NULLS NOT DISTINCT is available // This example demonstrates both available usages export const userNulls = pgTable("user_nulls_example", { id: integer(), id2: integer().unique("custom_name", { nulls: "not distinct" }), }, (t) => [ unique().on(t.id).nullsNotDistinct() ]); ``` [source]
export const user = singlestoreTable('user', { id: int('id').unique(), }); [source]
export const table = singlestoreTable('table', { id: int('id').unique('custom_name'), }); [source]
export const composite = singlestoreTable('composite_example', { id: int('id'), name: varchar('name', { length: 256 }), }, (t) => [ unique().on(t.id, t.name), unique('custom_name').on(t.id, t.name) ]); ``` [source]
export const user = sqliteTable('user', { id: int('id').unique(), }); [source]
export const table = sqliteTable('table', { id: int('id').unique('custom_name'), }); [source]
export const composite = sqliteTable('composite_example', { id: int('id'), name: text('name'), }, (t) => [ unique().on(t.id, t.name), unique('custom_name').on(t.id, t.name) ]); ``` [source]
In Prisma v6, the `UNIQUE INDEX` is changing into a `PRIMARY KEY`: [source]
The output reports the planned operations: creating the `users` and `posts` collections and a unique index on `users.email`. [source]
| Name | Required | Type | Description | | ----------- | -------- | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ [source]
The `sort` argument can also be used on compound indexes: [source]
As an example, the following model adds a `Gin` index to the `value` field, with `JsonbPathOps` as the class of operators allowed to use the index: [source]
As an example, the following model adds a `Gist` index to the `value` field with `InetOps` as the operators that will be using the index: [source]
The BRIN index type is useful if you have lots of data that does not change after it is inserted, such as date and time values. If your data is a good fit for the index, it can store large datasets in a minimal space. [source]
As an example, the following model adds a `Brin` index to the `value` field with `Int4BloomOps` as the operators that will be using the index: [source]
A table can have at most one clustered index. [source]
> [!NOTE] > Partial indexes are supported on **PostgreSQL**, **SQLite**, **SQL Server**, and **CockroachDB**. They are **not** supported on MySQL. [source]
1. Automatically add `"partialIndexes"` to the `previewFeatures` list in your generator block 2. Represent the partial index predicate using the `raw()` syntax with the database's normalized form of the SQL expression [source]
* Table name: `_CategoryToPost` (underscore + model names alphabetically + `To`) * Columns: `A` (FK to first model alphabetically) and `B` (FK to second) * Unique index on both columns, non-unique index on `B` [source]
Drizzle has simple and flexible API, which lets you easily create such an index using SQL-like syntax: <CodeTabs items={["schema.ts", "migration.sql"]}> <CodeTab> ```ts copy {12,13} import { SQL, sql } from 'drizzle-orm'; import { AnyPgColumn, pgTable, serial, text, uniqueIndex } from 'drizzle-orm/pg-core'; [source]
Drizzle has simple and flexible API, which lets you easily create such an index using SQL-like syntax: <CodeTabs items={["schema.ts", "migration.sql"]}> <CodeTab> ```ts copy {12,13} import { SQL, sql } from 'drizzle-orm'; import { AnyMySqlColumn, mysqlTable, serial, uniqueIndex, varchar } from 'drizzle-orm/mysql-core'; [source]
// custom lower function export function lower(email: AnySQLiteColumn): SQL { return sql`lower(${email})`; } ``` </CodeTab> ```sql CREATE TABLE `users` ( `id` integer PRIMARY KEY NOT NULL, `name` text NOT NULL, `email` text NOT NULL ); --> statement-breakpoint CREATE UNIQUE INDEX `emailUniqueIndex` ON `users` (lower(`email`)); ``` </CodeTabs> [source]
```typescript // Index declaration reference index("name") .on(table.name) .algorithm("default") // "default" | "copy" | "inplace" .using("btree") // "btree" | "hash" .lock("default") // "none" | "default" | "exclusive" | "shared" ``` [source]
<Section> ```typescript {8-9} import { integer, text, index, uniqueIndex, sqliteTable } from "drizzle-orm/sqlite-core"; [source]
A B-tree index can be created on a `CITEXT` column just like a `TEXT` column: [source]
* hstore is generally more efficient than JSON for simple key-value pairs * GIN indexes can significantly improve query performance on hstore columns * The storage size of hstore is typically smaller than equivalent JSON storage [source]
* GiST indexes significantly improve range query performance * IP address operations are very efficient as they use native integer comparisons * Range operations are optimized for both IPv4 and IPv6 * Indexes work well with both IP versions in the same column [source]
* **Fast Full-Text Search**: Efficient searching using bigram matching * **Similarity Calculation**: Built-in functions to measure string similarity * **Partial Matching**: Find strings containing specific patterns * **Language Agnostic**: Works well with any language, including non-Latin scripts * **GIN Index Support**: Fast search performance using GIN indexes [source]
* `pg_bigm.similarity_threshold`: Default similarity threshold (0.0 to 1.0) * `pg_bigm.enable_recheck`: Whether to recheck similarity in search results * `pg_bigm.gin_key_limit`: Maximum number of bigrams for GIN index [source]
1. **Indexing**: * Always create spatial indexes (GiST) on geometry columns * Use appropriate coordinate systems for your use case [source]
1. Create GiST indexes on seg columns: [source]
2. Common operators that can use the GiST index: [source]
B-Tree indexes play a crucial role in enhancing database performance by allowing faster retrieval of specific rows. Imagine them as the index pages in a book, providing quick references to relevant data. [source]
* B-tree indexes are organized as balanced tree structures. * Each level of the tree acts like a doubly-linked list of pages. * The index starts with a metapage at the beginning of the first segment file. * All other pages are either leaf pages (the lowest level) or internal pages. [source]
Function form of dot notation — `union_extract(col, 'variant')` is equivalent to `col.variant`. Primarily useful in expression indexes: [source]
For partial unique indexes (indexes with a `WHERE` clause), the conflict target must also include a `WHERE` clause that matches the index's condition. [source]
For example, the following image shows a compound index where documents are first sorted by `userid` in ascending order (alphabetically). Then, the `scores` for each `userid` are sorted in descending order: [source]
This section describes technical details and limitations for compound indexes. [source]
Indexes store references to fields in either ascending (`1`) or descending (`-1`) sort order. For compound indexes, sort order can determine whether the index supports a sort operation. For more information, see [Compound Index Sort Order.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order/#std-label-index-compound-sort-order) [source]
For example, to create a unique index on the `email` field of the `users` collection, use the following operation in `mongosh`: [source]
For example, to create a unique index on `name`, `email`, and `password` fields of the `users` collection, use the following operation in `mongosh`: [source]
The unique index permits the insertion of the following documents into the collection since the index enforces uniqueness for the *combination* of `email` and `name` values: [source]
Unique indexes ensure that a value appears at most once for a given field. [source]
This example adds a unique index on the `user_id` field of a `members` collection to ensure that there are no duplicate values in the `user_id` field. [source]
MongoDB supports creating indexes on a field, or set of fields, to improve performance for queries. MongoDB supports [flexible schemas](https://www.mongodb.com/docs/manual/data-modeling/#std-label-manual-data-modeling-intro), meaning document field names may differ within a collection. Use wildcard indexes to support queries against arbitrary or unknown fields. [source]
- If your application queries a collection where field names vary between documents, create a wildcard index to support queries on all possible document field names. - If your application repeatedly queries an embedded document field where the subfields are not consistent, create a wildcard index to support queries on all of the subfields. - If your application queries documents that share common characteristics. A compound wildcard index can efficiently cover many queries for documents that have common fields. To learn more, see [Compound Wildcard Indexes.](https://www.mongodb.com/docs/manual [source]
| Index | Default Name | |---|---| | `{ score : 1 }` | `score_1` | | `{ content : "text", "description.tags": "text" }` | `content_text_description.tags_text` | | `{ category : 1, locale : "2dsphere"}` | `category_1_locale_2dsphere` | | `{ "fieldA" : 1, "fieldB" : "hashed", "fieldC" : -1 }` | `fieldA_1_fieldB_hashed_fieldC_-1` | [source]
Multikey indexes collect and sort data stored in arrays. [source]
This image shows a multikey index on the `addr.zip` field: [source]
Wildcard indexes apply to collections with flexible schemas, where document field names may differ. Use wildcard indexes to support queries against arbitrary or unknown field names. [source]
Geospatial indexes improve performance for queries on geospatial coordinate data. To learn more, see [Geospatial Indexes.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-geospatial/#std-label-geospatial-index) [source]
Hashed indexes support [hashed sharding](https://www.mongodb.com/docs/manual/core/hashed-sharding/#std-label-sharding-hashed-sharding). Hashed indexes index the hash of a field's value. [source]
Clustered indexes specify the order in which [clustered collections](https://www.mongodb.com/docs/manual/core/clustered-collections/#std-label-clustered-collections) store data. Collections created with a clustered index are called clustered collections. [source]
2d indexes support certain query operators that calculate distances using spherical geometry. Spherical query operators use radians for distance. To use spherical query operators with a 2d index, you must convert distances to radians. [source]
2d indexes support the following spherical query operators: [source]
The default location bounds for 2d indexes allow latitudes less than -90 and greater than 90, which are invalid values. The behavior of geospatial queries with these invalid points is not defined. [source]
Defining a smaller location range for a 2d index reduces the amount of data stored in the index, and can improve query performance. [source]
The index covers a smaller location range and has increased performance than a default 2d index. [source]
While 2d indexes do not support more than one location field in a document, you can use a [multi-key index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/#std-label-index-type-multi-key) to index multiple coordinate pairs in a single document. For example, in the following document, the `locs` field holds an array of coordinate pairs: [source]
- To perform proximity queries on a spherical surface, see [Query for Locations Near a Point on a Sphere.](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2dsphere/query/proximity-to-geojson/#std-label-2dsphere-query-geojson-proximity) [source]
2dsphere indexes are available in the following versions: [source]
| 2dsphere Index Version | Description | |---|---| | Version 4 | MongoDB 8.3 introduces version 4 of 2dsphere indexes. Version 4 is the default version for 2dsphere indexes created in MongoDB 8.3 and later. | | Version 3 | MongoDB 3.2 introduces version 3 of 2dsphere indexes. Version 3 is the default version for 2dsphere indexes created in MongoDB 3.2 and later. | | Version 2 | MongoDB 2.6 introduces version 2 of 2dsphere indexes. Version 2 is the default version for 2dsphere indexes created in MongoDB 2.6 to 3.0. | | Version 1 | MongoDB 2.4 introduces version 1 of 2dsphere indexes. MongoDB 2. [source]
The following command creates a version 2 2dsphere index on the `address` field: [source]
The following operation creates an ascending multikey index on the `test_scores` field of the `students` collection: [source]
For example, consider a compound index `{ temperature: 1, humidity: 1 }` with the following bounds: [source]
After you create the compound index, `$text` queries only scan documents that match a specified equality condition on the `department` field. [source]
The following operation creates a wildcard index on all document fields in the `artwork` collection (excluding `_id`): [source]
If there are document fields that you rarely query, you can create a wildcard index that omits those fields. [source]
The following operation creates a wildcard index on all document fields in the `products` collection, but omits the `attributes.memory` field from the index: [source]
Wildcard indexes do not replace workload-based index planning. [source]
This example creates a compound wildcard index on the `salesData` collection: [source]
The wildcard index term, `"$**"`, specifies every field in the collection. The `wildcardProjection` limits the index to the specified fields, `"customFields.addr"` and `"customFields.name"`. [source]
The following example creates a partial compound wildcard index on the `tenantId` field and on all the sub-fields in the `customFields` field, only on documents with a `tenantRegion` of `1`. [source]
The wildcard index continues traversing any additional embedded objects or arrays until it reaches a primitive value. It then indexes the primitive value, along with the full path to that field. [source]
A wildcard index that includes the `account` field descends into the `account` object to traverse and index its contents: [source]
A wildcard index which includes the `ship` field descends into the object to traverse and index its contents: [source]
Wildcard indexes do not record the array position of any given element in an array during indexing. However, MongoDB may still use the wildcard index to fulfill a query that includes a field path with one or more explicit array indices. [source]
Sharded collections require an index that supports the [shard key](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-shard-key). The index can be an index on the shard key or a [compound index](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-compound-index) where the shard key is a [prefix](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-compound-index-prefix) of the index. [source]
- If the collection is empty, [`sh.shardCollection()`](https://www.mongodb.com/docs/manual/reference/method/sh.shardCollection/#mongodb-method-sh.shardCollection) creates the unique index on the shard key if such an index does not already exist. - If the collection is not empty, you must create the index first before using [`sh.shardCollection()`.](https://www.mongodb.com/docs/manual/reference/method/sh.shardCollection/#mongodb-method-sh.shardCollection) [source]
If after the evaluation, the user decides to drop the index, you can drop the hidden index; i.e. you do not need to unhide it first to drop it. [source]
For more information on hidden indexes, see [Hidden Indexes.](https://www.mongodb.com/docs/manual/core/index-hidden/) [source]
The following option is available for [2dsphere](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2dsphere/#std-label-2dsphere-index) indexes only: [source]
| Parameter | Type | Description | |---|---|---| | `2dsphereIndexVersion` | integer | Optional. The `2dsphere` index version number. Users can use this option to override the default version number. For the available versions, see [2dsphere Indexes.](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2dsphere/#std-label-2dsphere-v2) | [source]
[Wildcard indexes](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-wildcard/#std-label-wildcard-index-core) can use the `wildcardProjection` option. [source]
- To change the `hidden` option for an index to`true` , use the[`db.collection.hideIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.hideIndex/#mongodb-method-db.collection.hideIndex) method:db.movies.hideIndex( { title: 1 } ) - To change the `hidden` option for an index to`false` , use the[`db.collection.unhideIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.unhideIndex/#mongodb-method-db.collection.unhideIndex) method:db.movies.unhideIndex( { title: 1 } ) [source]
For example, you can use the following code to create a compound index on the `movies` collection of the `sample_mflix` database specifying the numeric fields `year` and `metacritic` and the string field `title`. The index also specifies the collation locale `"fr"` for string comparisons: [source]
The following operation creates a wildcard index on the `awards` field: [source]
With this wildcard index, MongoDB indexes all scalar values of `awards`. If the field is a nested document or array, the wildcard index recurses into the document or array and indexes all scalar fields in the document or array. [source]
The wildcard index can support arbitrary single-field queries on `awards` or one of its nested fields: [source]
The following operation creates a wildcard index on all scalar fields (excluding the `_id` field): [source]
The following operation creates a wildcard index and uses the `wildcardProjection` option to include only scalar values of the `tomatoes.viewer` and `tomatoes.critic` fields in the index. [source]
This example uses a wildcard index and a `wildcardProjection` document to index the scalar fields for each document in the collection. [source]
The wildcard index excludes the `tomatoes.viewer` and `tomatoes.critic` fields: [source]
The following example creates two indexes on the `restaurants` collection: an ascending index on the `borough` field and a [2dsphere](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2dsphere/#std-label-2dsphere-index) index on the `location` field. [source]
The following operation creates a wildcard index on the `product_attributes` field: [source]
With this wildcard index, MongoDB indexes all scalar values of `product_attributes`. If the field is a nested document or array, the wildcard index recurses into the document/array and indexes all scalar fields in the document/array. [source]
The wildcard index can support arbitrary single-field queries on `product_attributes` or one of its nested fields: [source]
Wildcard indexes omit the `_id` field by default. To include the `_id` field in the wildcard index, you must explicitly include it in the `wildcardProjection` document. See [parameter documentation](https://www.mongodb.com#std-label-createIndexes-method-wildcard-option) for more information. [source]
The following operation creates a wildcard index and uses the `wildcardProjection` option to include only scalar values of the `product_attributes.colors` and `product_attributes.material` fields in the index. [source]
This example uses a wildcard index and a `wildcardProjection` document to index the scalar fields for each document in the collection. The wildcard index excludes the `product_attributes.colors` and `product_attributes.material` fields: [source]
For example, add the following two compound indexes. The first index orders by `quantity` field first, and then the `type` field. The second index orders by `type` first, and then the `quantity` field. [source]
A [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound) references multiple fields and can dramatically improve query response times. [source]
- To ensure the `bank` and`number` fields do not repeat, make the index[unique.](https://www.mongodb.com/docs/manual/core/index-unique/#std-label-index-type-unique) - To allow indexing of multiple fields, make the index [compound.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/#std-label-index-type-compound) - To allow indexing of documents inside an array, make the index of the type [multikey.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/#std-label-index-type-multikey) [source]
Defines a compound [unique constraint](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/models#defining-a-unique-field) for the specified fields. [source]
> **Note**: Before the `partialIndexes` Preview feature (and before version 4.0.0 / 3.5.0 with the `extendedIndexes` Preview feature), the signature was: > > ```prisma no-lines > @@unique(_ fields: FieldReference[], name: String?, map: String?) > ``` [source]
* models, fields, and relations, plus how they map to tables and columns * storage details: primary keys, unique constraints, indexes, and foreign keys * named types, enums, and value objects * types and capabilities contributed by extension packs, such as pgvector's `Vector` * content hashes that identify this exact version of the schema [source]
* In MySQL/MariaDB, you can specify sort order (`ASC`/`DESC`) directly in unique constraints and indexes * In PostgreSQL, sort order can only be specified on indexes, not on unique constraints * In SQL Server, sort order is supported on all constraints and indexes including `@id` and `@@id` [source]
- Create namespaces and attributes - Add indexes and unique constraints - Model relationships - Lock down your schema for production [source]
@@unique([firstname, lastname, id]) } ``` [source]
Composite IDs and compound unique constraints can be defined in your Prisma schema using the [`@@id`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference) and [`@@unique`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference) attributes. [source]
* `@id` or `@@id` for primary key * `@unique` or `@@unique` for unique constraint [source]
> **Note**: Before the `partialIndexes` Preview feature, the signature was: > > ```prisma no-lines > @unique(map: String?, length: number?, sort: String?, clustered: Boolean?) > ``` [source]
export const users = pgTable( 'users', { id: serial('id').primaryKey(), name: text('name').notNull(), email: text('email').notNull(), }, (table) => [ // uniqueIndex('emailUniqueIndex').on(sql`lower(${table.email})`), uniqueIndex('emailUniqueIndex').on(lower(table.email)), ], ); [source]
export const users = mysqlTable( 'users', { id: serial('id').primaryKey(), name: varchar('name', { length: 255 }).notNull(), email: varchar('email', { length: 255 }).notNull(), }, (table) => [ // uniqueIndex('emailUniqueIndex').on(sql`(lower(${table.email}))`), uniqueIndex('emailUniqueIndex').on(lower(table.email)), ] ); [source]
export const users = sqliteTable( 'users', { id: integer('id').primaryKey(), name: text('name').notNull(), email: text('email').notNull(), }, (table) => [ // uniqueIndex('emailUniqueIndex').on(sql`lower(${table.email})`), uniqueIndex('emailUniqueIndex').on(lower(table.email)), ] ); [source]

Related concepts

unique constraint — is a near-synonym of Index types; backed by a unique index in every engine here
index build — is a dependent of Index types
unique index — is a hyponym of Index types
wildcard index — is a hyponym of Index types
B-tree — is a hyponym of Index types
GiST index — is a hyponym of Index types; Postgres
partial index — is a hyponym of Index types
compound index — is a hyponym of Index types; MongoDB 'compound' = SQL 'composite'/'multicolumn'
GIN index — is a hyponym of Index types; Postgres
@@index — is a instance of Index types; Prisma schema index attributes
geospatial index — is a hyponym of Index types
index definition — is a part of Index types
multikey index — is a hyponym of Index types
BRIN index — is a hyponym of Index types; Postgres
hidden index — is a hyponym of Index types
hashed index — is a hyponym of Index types
sparse index — is a hyponym of Index types
expression index — is a hyponym of Index types
unused index — is a problem of Index types
clustered index — is a hyponym of Index types