The terms "sargable" and "non-sargable" focus to the search arguments used in the query's WHERE clause.
Sargable Query:
A sargable query, short for "Search ARGument ABLE," is a query that can take advantage of indexes effectively to optimize the search process. Sargable queries use search arguments that allow the database engine to seek and efficiently narrow down the results using indexes.
Example of a sargable query
In this example, the search argument "age = 25" is sargable because it can make use of an index on the "age" column to quickly retrieve the matching rows.
Non-Sargable Query:
A non-sargable query, on the other hand, is a query where the search arguments cannot fully utilize indexes, leading to inefficient query execution. Non-sargable queries often involve applying functions or operations to the column being searched, which prevents the use of indexes effectively.
Recommended by LinkedIn
Example of a non-sargable query:
In this example, the search argument "YEAR(date_of_birth) = 1990" is non-sargable because the use of the YEAR function on the "date_of_birth" column prevents the database engine from using an index effectively. Instead, it may need to perform a full table scan to evaluate the condition.
Non-sargable queries can significantly impact query performance, especially when dealing with large datasets, as they can lead to slower execution times and increased resource usage.
It's generally recommended to write sargable queries whenever possible to ensure optimal performance. By avoiding functions or operations on columns in the WHERE clause, you increase the chances of the query being sargable and benefiting from indexes effectively.
How to write Sargable Query?
