Indexes

An index helps the database find rows without reading the whole table. It works like the index at the back of a book: to find a topic, you look it up and go straight to the page instead of reading every page. Indexes don't change your data or the results of a query, only how quickly the answer arrives.

When an index helps

Without an index on customer_id, finding one customer's orders means checking every row of orders. That is fine for a few hundred rows and slow for a few million. With an index, the database goes straight to the matching rows.

Consider an index on columns that you often:

• Filter on, such as status or ordered_on.
• Join on, especially foreign keys such as customer_id.
• Sort by, such as created_at when you regularly ask for the latest rows.

Small tables rarely need one. A good sign that you do is a query that keeps getting slower or is stopped at the 10-second limit.

What an index costs

Indexes aren't free, which is why not every column has one:

• Slower writes. Every insert, every delete and every update to an indexed column also has to update the index.
• Storage. An index takes up space, and that space counts towards your storage quota.

Add indexes for the queries you actually run, and remove the ones nothing uses any more.

Indexes on several columns

An index can cover up to 8 columns, and their order matters. An index on customer_id and then ordered_on is sorted by customer first, and by date within each customer, like a phone book sorted by last name and then first name:

A phone book doesn't help you find everyone with a given first name, and this index doesn't help you find every order on a given date. Put the column you always filter on first.

Unique indexes

A unique index also refuses duplicates. On several columns, it applies to the combination. A unique index on order_id and product_id together allows each product once per order, while the same product can still appear in other orders.

For a single column, mark the column unique instead. This creates the index for you.

What you don't need to index

Some columns are already indexed or can't be:

• Primary keys and unique columns already have an index.
• Foreign keys don't. An index on customer_id isn't created for you, and is usually worth adding.
• text columns can only be indexed on their first characters (up to 768), so string is a better type for values you look up.
• json columns can't be indexed directly.

An index also can't help with a search for text in the middle of a value. Finding names that start with "An" can use an index on name, while finding names that contain "ova" has to read every row.

Adding an index in Tunori

Ask in the chat on the Database page, for example:

Finding a customer's orders is getting slow. Which index would help?

The assistant can ask the database how it runs a query, which is called the query plan, and see whether it reads the whole table or uses an index. Creating or removing an index needs an organization Admin, or an agent with Full database access, and asks for approval first. The table stays usable while the index is being built in most cases.

The indexes of each table are listed with its columns in the schema on the Database page.