> Portal Navigation:
>
> - Append `.md` to any URL under `https://dev.wix.com/docs/` to get its markdown version.
> - Pages are either content pages (article or reference text) or menu pages (a list of links to child pages).
> - To get a menu page, truncate any URL to a parent path and append `.md` (e.g. `https://dev.wix.com/docs/sdk.md`, `https://dev.wix.com/docs/sdk/core-modules.md`).
> - Top-level index of all portals: https://dev.wix.com/docs/llms.txt
> - Full concatenated docs: https://dev.wix.com/docs/llms-full.txt
## Resource: Indexes and Wix Data Collections
## Article: Indexes and Wix Data Collections
## Article Link: https://dev.wix.com/docs/develop-websites/articles/databases/wix-data/collections/indexes-and-wix-data-collections.md
## Article Content:
# Velo: Indexes and Wix Data Collections
**Important:**
Additional indexes are available for sites with premium plans. If you need additional indexes, you can [upgrade your site](https://www.wix.com/upgrade/website). Learn more about [premium plans](https://dev.wix.com/docs/develop-websites/articles/coding-with-velo/premium-plans/about-premium-plans.md).
Wix data collections give you the ability to add indexes to your collections. Use indexes to speed up data retrieval times and manage your data.
## What is an index?
An index is a set of keys used to improve performance when searching for data in a database. Indexes work by using a lookup key to quickly locate the requested data without having to search the entire data collection.
For example, in a textbook, you look in the index for the subject that you are interested in, then go directly to the page number without searching through the whole book. An index in a database works the same way. It helps the database go directly to the requested item without scanning through the whole collection.
You can create a database index on a single field or on multiple fields. If a single field is not specific enough to help find your data, adding additional fields to the index can make it faster.
Indexes have a sort order. When you define an index, you specify whether the index is ascending or descending, based on how you regularly access the data.
A unique index is a special index that enforces the uniqueness of the field that it is defined for. If the index is a unique index, only one item in the collection can have a given value for the indexed field.
## What can I do with an index?
Use indexes to speed up data retrieval and get faster response times for database reads on your site. You can also use indexes to avoid duplicate data in your database. By creating a unique index for a given field, the database rejects new or updated items that have the same value for the index field as an existing item.
Indexes also speed up sorting when the fields use `.eq` filters and `.ascending` or `.descending`.
Add a unique index on a data collection to prevent duplicates.
While indexes greatly enhance data retrieval speeds, they can slow down the write speed slightly. The write speed for creating and updating items is a bit slower because the database has to write the index as well as the item data. Additionally, there is a small increase in the amount of data you have to store because your database is now storing the index as well as the item data.
**When to add an Index**
Using an index can improve your query speeds if:
- You have a large data collection and your query times are slow.
- You have queries that are used often and need to return data quickly.
- Your queries are not slow yet, but your collection is expected to grow significantly.
**When not to add an Index.**
Indexes are not always necessary. You don't need to add an index in the following cases:
- If you write to the collection frequently, but it's not read by the main application. For example, a log file.
- If the collection is not expected to grow significantly.
- If the collection already has an index.
> **Note:**
> All Wix collections are created with 2 indexes:
>
> - \_id ascending
> - \_createdDate descending, \_id descending.
## Indexes and your Wix collections
See [Adding an Index for Your Collection](https://support.wix.com/en/article/content-manager-adding-an-index-for-your-collection) to learn how to add an index to your Wix collections.
You can create both regular and unique indexes for Wix sites:
- Regular indexes can include up to 3 fields.
- Unique indexes ensure that none of the items in your collection have identical values for the indexed field. Unique indexes can include only 1 field. Unique indexes also function like a regular index.
The number of indexes you can add to your site depends on the [premium plan](https://www.wix.com/upgrade/website) you have. Learn more about the [data platform resources](https://dev.wix.com/docs/develop-websites/articles/coding-with-velo/limits-and-optimization/data-features.md) available for premium plans.
> **Notes:**
>
> - A unique index also functions like a regular index.
> - Some premium plans include an additional 8 regular indexes per collection.
### Which fields make good indexes?
Which indexes to create and which fields those indexes contain, is determined by the queries that your site uses. Define your indexes using the fields that you would search by, and that are commonly used in your site's queries.
For example, in the product collection, if your site visitors often search for products by name, add an index on product name.
The index makes querying by product name faster.
```javascript
wixData.query("Products").eq("productName", "Watermelon");
```
If your site visitors browse by category and price, add an index on`category`(ascending) and`price`(ascending).
The index speeds up the querying by both fields.
```javascript
wixData.query("Products").eq("category", "Fruit").eq("price", 1.39);
```
In big collections, it may not be possible to get items in order, especially when fetching by page using `.skip()`. In the example below, the database skips the first 5000 items, then performs the sort. Ordering by an index solves this problem as the database sorts first, and then skips the first 5000 items.
```javascript
wixData.query("Products").skip(5000).ascending("category").ascending("price");
```
You are not restricted to the index fields in your query. You can use other fields in the query and still get the benefit of the index. Additionally, the order that you specify the fields in your query does not matter.
```javascript
wixData
.query("Products")
.eq("category", "Fruit")
.gt("price", 1.29)
.eq("origin", "Spain");
```
### Which fields make bad indexes?
Indexes work best when the fields that they are defined for have a diverse range of values. Fields that have a narrow range of values, like booleans, do not make good indexes.
If color is a common search filter, adding an index on product color makes those queries faster. If however, you only offer black or white products, the index does not result in any improvement in your query speeds.
### Working with unique indexes
When adding a unique index, if the field that you are using for your index is not unique, an error occurs. Wix Data functions that write to your collection throw errors if the unique index constraint is violated.
For [save()](https://www.wix.com/velo/reference/wix-data/save), [insert()](https://www.wix.com/velo/reference/wix-data/insert), and [update()](https://www.wix.com/velo/reference/wix-data/update)), the operation will fail and the collection will not be modified if the index constraint is violated.
For bulk functions, [bulkInsert()](https://www.wix.com/velo/reference/wix-data/bulkinsert), [bulkSave()](https://www.wix.com/velo/reference/wix-data/bulksave), and [bulkUpdate()](https://www.wix.com/velo/reference/wix-data/bulkupdate), items that violate the unique index constraint are not modified and are added to the errors array of the response object. Items that do not violate the index constraint are updated or added to the collection.
### Supported operators
Indexes improve query speeds for the following logical operators:
- eq
- gt
- gte
- lt
- lte
- hasSome
The [filter()](https://www.wix.com/velo/reference/wix-data/wixdataaggregate/filter) function in [WixDataAggregate](https://www.wix.com/velo/reference/wix-data/wixdataaggregate/introduction) makes use of indexes.
Indexes also speed up [distinct](https://www.wix.com/velo/reference/wix-data/wixdataquery/distinct) queries.
### Supported data types
You can define indexes on all scalar data types:
- Text
- Number
- String
- Boolean
Other Wix Data types that map to the above primitive types are also supported, for example, URLs.
Indexes work with arrays that hold scalar data types, for example Tags.
### Using more than 1 index
A collection is limited to 3 indexes. However, 3 indexes may not be enough in cases where your site visitor can choose a number of filters, and some filters may be left blank.
For example, 3 fields: `name`, `phone`, and `email` can be filtered in the following combinations:
1. `name`
2. `phone`
3. `email`
4. `name, phone`
5. `name, email`
6. `phone, email`
7. `name, phone, email`
There are 7 combinations but only three indexes available.
In this case define 3 indexes as follows:
- Index 1: `name`
- Index 2: `phone`
- Index 3: `email`
The Wix Data engine chooses one, or combines several of the indexes to speed up the query.
### Writing queries to make use of indexes
Not all queries will make the best use of your indexes, but if you write your query correctly, the performance can be greatly improved.
Use the following principles when writing queries so that they make use of indexes:
- If your index has multiple fields, use as many as possible. If you do not use all of the fields in the index, you must include them in the order that they were specified. For example, if an index is defined for `name`, `phone`, and `email`, filtering on `name`, or on `name` and `phone` will use the index, but filtering on `phone` and `email` will not.
- To use a field for ordering, all fields defined before it in an index must appear in `.eq` filters or ordering clauses.
- To use a field in range filter (`.gt, .lt, .ge, .le`) all fields defined before it in an index must appear in `.eq` filters.
- Ordering will use an index if ordering directions for the fields match index direction.
The following examples show when an index defined for `category(ascending)`and `price(ascending)`speeds up the query and when it does not.
**Equality**
The following query makes full use of the index:
```javascript
wixData.query("Products").eq("category", "Fruit").eq("price", 1.39);
```
**Range Filters**
The index speeds up range queries on `price`, when filtering by `category:`
```javascript
wixData.query("Products").eq("category", "Fruit").gt("price", 1.5);
```
The index will not speed up range queries on `price`, when filtering by another field, like `size`.
In the following query, only the `category` field filter would benefit from an index. This is because in order for the filter for `price` to use the index, all fields defined before it in the index must appear in `.eq` filters, and `category` is using a `.gt` filter.
```javascript
wixData.query("Products").gt("category", "C").gt("price", 1.4);
```
**Partial Index**
The index will also speed up queries on a subset of the indexed fields, as long as they are used in the order that they are defined in the index.
For example, the following query will be improved by the index:
```javascript
wixData.query("Products").eq("category", "Fruit");
```
The query below however, will not be faster because the `category` field is defined first in the index and must be present in the query.
```javascript
wixData.query("Products").gt("price", 1.5);
```
Given the following index:
`brand: ascending, name: ascending, color: ascending`
The following filters will make efficient use of the index
- `eq('brand', …)`
- `eq('brand', …) .eq('name', …)`
- `eq('brand', …) .eq('name’, …) .eq('color',`…)
**Sorting**
Indexes also speed up ordering when the fields use `.eq` filters, and `.ascending` or `.descending`.
This query will return faster:
```javascript
wixData.query("Products").eq("category", "Fruit").ascending("price");
```
This query will not run any faster as the `category` field filter is using `.gt` not `.eq`:
```javascript
wixData.query("Products").gt("category", "Fruit").ascending("price");
```
## Automatic indexes
For large collections with high query volumes, Wix automatically identifies slow-running queries and creates indexes to improve performance. [Automatic indexes](https://support.wix.com/en/article/cms-adding-an-index-for-your-collection#about-automatic-indexes) are created behind the scenes to optimize data retrieval without any action required from you.
## Available indexes
Basic plans and most premium plans support 4 indexes, 3 regular and 1 unique. Certain tier premium plans support 15 indexes, 5 regular (up to 3 fields), 2 unique, and 8 additional regular fields (1 field). If you need additional indexes than your plan offers, you can [upgrade your site](https://www.wix.com/upgrade/website).