Queries on unindexed columns

Optimize provides recommendations to help you identify and resolve performance issues caused by missing database indexes.

The following queries targeting the User model use a where property to filter on columns that do not have indexes:

await prisma.user.findFirst({
  where: {
    name: "Marc",
  },
});

await prisma.user.findFirst({
  where: {
    name: "Jon",
  },
});

await prisma.user.count({
  where: {
    name: "Nikolas",
  },
});

What is the problem?

An index allows the database to retrieve data more quickly, similar to how an index in a book helps you locate information without reading every page.

When using Prisma with a where property, if no indexes are defined for the relevant columns, the database may need to scan every row in the table (a “full table scan”) to find matches. This can be undesirable for several reasons:

User experience

For large datasets, if the database must scan the entire table to find matching rows, users will experience longer waiting times.

Resource utilization

• High CPU usage: Scanning large tables can significantly increase CPU usage, degrading overall system performance.
• Memory consumption: More memory is required to process and store data during a full table scan.
• Disk I/O: Full table scans increase disk input/output operations, potentially slowing down other database activities.

:::warning

While these issues might not appear in development due to smaller datasets, they can become significant problems in production, where datasets are typically much larger.

:::

More on database indexes

How indexes work

Indexes create a data structure that stores the indexed column's values along with pointers to the corresponding rows in the table. When you query the database using an indexed column, the database can use this index to quickly locate the relevant rows instead of scanning the entire table.

The trade-offs of indexing

• Space vs. time: Indexing requires additional storage space to save index data, but it significantly speeds up data retrieval.
• Update overhead: Every time data is added to, updated in, or removed from your table, there is an overhead to keep the indexes up to date, which can slow down write operations.

When to use indexes

• Large datasets: Indexes are particularly beneficial for tables with a large number of rows.
• Frequent queries with filtering or sorting: Use indexes on columns that are frequently used for filtering or sorting.
• Looking up related data: Use indexes on foreign key columns to speed up the retrieval of related records, such as when using include.

When not to use indexes

• Small tables: For tables with very few rows, the overhead of maintaining indexes might not be worth the performance gain.
• Write-heavy tables: Indexes can slow down write operations (create, update, delete) because the index needs to be updated as well. Avoid excessive indexing on models with frequent write operations.
• Infrequently accessed tables: If a table is rarely accessed, the benefits of indexing may not justify the overhead.
• Columns with large data: Indexing columns with large data can lead to higher storage requirements and might not provide significant performance improvements.
• Rarely filtered columns: If a table is often accessed but rarely filtered by a specific column, creating an index on that column may not be beneficial.

:::warning

Even if you index a column, the database may not always use it. Many database management systems, such as PostgreSQL and MySQL, have a query optimizer that evaluates multiple execution plans and selects the one it estimates to be most efficient. In some cases, this may involve ignoring an existing index in favor of a different execution plan that it determines will perform better for that specific query.

:::