Translate the following text to English language: undefined

Improving Query Performance with Prisma Indexes#

Introduction#

What is a Database Index#

A database index is a smaller auxiliary data structure used by a database server to store a subset of table data. Indexes are typically used to improve the read performance of a given table.

Indexes consist of key-value pairs:

key: the column used to create the index
value: a pointer to a specific record in the table

Multiple columns can be indexed in a table. For example, if there is a table named User with three columns: id, firstName, and lastName, an index can be created on the firstName and lastName columns.

Types of Database Indexes#

Index (default): a regular, non-unique index that does not enforce any constraints on the data
Primary Key: a unique identifier for rows in a table
Unique Indexes: enforce the uniqueness of values in a column, preventing duplicates
Full-text Indexes: used for text columns and support full-text search

Directly Retrieving Records from Index Scans#

Databases can also choose to return matching records directly from an index without the need to "query" the original table. This is known as an index scan.

SELECT firstName from 'User' where firstName = 'Jimmy';

The Cost of Fast Read with Indexes#

Indexes are widely believed to be effective in improving read performance, but they come with a cost.

Write operations incur additional overhead because indexes need to be updated with each write operation.

Another cost of indexes is that they require additional resources from the database server for maintenance. Indexes require extra storage, memory, and I/O from the database server.

Improving Query Performance with B-Tree Indexes#

Time Complexity of B-Trees#

Sequential scans have linear time complexity (O(n)). This means that the time it takes to retrieve a record grows linearly with the number of records you have.

On the other hand, B-trees have logarithmic time complexity (O log(n)). This means that as the data size grows, the cost of retrieving records increases at a significantly slower rate.

Adding Indexes for Full Scans#

First Query without Index (216ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String
}

Second Query with firstName Index (59ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName])
}

Third Query with Composite Index (17ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName, lastName])
}

Fourth Query with Indexed and Sorted (11ms)

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName("desc"), lastName])
}

Improving Query Performance with Hash Indexes (PostgreSQL)#

Using Hash Indexes#

model User {
  id        Int    @id @default(autoincrement())
  firstName String
  lastName  String
  email     String

  @@index(fields: [firstName], type: Hash)
}

npx prisma migrate dev --name add-firstName-index

Original Article Link#

https://www.prisma.io/blog/improving-query-performance-using-indexes-1-zuLNZwBkuL#the-price-you-pay-for-faster-reads