Skip to main content

Full table scans caused by LIKE operations

Optimize provides recommendations to help you identify and resolve performance issues caused by full table scans from LIKE operations.

The following query targeting the User model provides contains and endsWith as options, which translate to LIKE and ILIKE SQL operators.

await prisma.user.findMany({ 
  where: { 
    email: { contains: "gmail.com" }, 
    name: { endsWith: "Burk" } 
  } 
})

What is the problem?​

LIKE and ILIKE operators in SQL can lead to full table scans, potentially impacting performance, especially with larger datasets:

UX​

  • Slower load times: Full table scans can significantly increase the time it takes to retrieve data, leading to longer wait times for users.

Resource utilization​

  • Increased resource usage: Full table scans increase CPU, memory usage, and disk I/O, straining system resources for your database.
  • Increased costs: In serverless database pricing plans, more intensive resource usage can translate into higher costs.