Improving query performance with Prisma (repost)

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 the 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 Only#

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-only scan.

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

The Cost of Fast Read with Indexes#

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

Write operations incur additional overhead. This is because every write operation requires updating the indexes.

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 is directly proportional to 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 Adding firstName Index (59ms)

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

  @@index(fields: [firstName])
}

Third Query Adding Composite Index (17ms)

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

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

Fourth Query Adding Sorted Index (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