TIL: What Are Sargable Queries?

While working on query optimization recently, I came across the concept of sargable queries — short for Search ARGument-able. It's all about writing SQL in a way that lets your database use indexes effectively, which can make a huge difference in performance.

For example:

-- Sargable
SELECT *
FROM AUTH_USER
WHERE age = 30;

This can use an index on age.

But if you wrap the column in a function, like this:

-- Not sargable
SELECT *
FROM AUTH_USER
WHERE YEAR(created_at) = 2023;

…it prevents the database from using the index on created_at.

Instead, a better (sargable) version would be:

-- Sargable
SELECT *
FROM AUTH_USER
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

Lesson

Avoid transforming columns in WHERE clauses if you want your queries to be fast and index-friendly.

Credit/Reference

Credit to ByteByteGo — I came across this while watching one of their videos and decided to dig deeper.