Sargable(Search ARGument ABLE) SQL Queries

Wanted to share something which can benefit most of us in our day to day queries

What are Sargable(Search ARGument ABLE) queries?

Queries where Database can make use of Index to speed up the execution

Bad - select * from table where year(order_date)=2023

Better - select * from table where order_date>='01-01-2022' and order_date<'01-01-2023'

Bad - select * from table where substring(product_name,4)='Tata'

Better - select * from table where product_name like 'Tata%'

Note -

1. Avoid using functions or calculations on indexed columns in the where clause
2. Use direct comparison if possible instead of wrapping the column in a function

Remeber all query engines are different, some are more suitable for column based operations and some are for rows based operations. Some are built for analytics datasets and some are for transactional data. It's important to understand the exection engine of database to optimize the query.

Don't assume a good query in MySQL will be good in AWS Redshift as well.

Some other things to consider to write effective SQL query are:

• Should you write CTE(Combined Table Expressions) or Sub Queries
• Index and it's types
• Long or wide table structure