Velo: Indexes and Wix Data Collections

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. 

Indexes and Your Wix Collections

See 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 you have. Learn more about the data platform resources available for premium plans.

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.

If your site visitors browse by category and price, add an index oncategory(ascending) andprice(ascending).

The index speeds up the querying by both fields.

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.

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.

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(), insert(), and update()), the operation will fail and the collection will not be modified if the index constraint is violated.

For bulk functions, bulkInsert(), bulkSave(), and 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() function in WixDataAggregate makes use of indexes.

Indexes also speed up 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:

Range Filters

The index speeds up range queries on price, when filtering by  category:

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.

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:

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.

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:

This query will not run any faster as the category field filter is using .gt not .eq: