Sargable

From Wikipedia, the free encyclopedia
Jump to: navigation, search

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.

A query failing to be sargable is known as non-sargable query and has an effect on query time, so one of the steps in query optimization is to convert them to be sargable.

The typical situation that will make a SQL query non-sargable is to include in the WHERE clause a function operating on a column value. The WHERE clause is not the only clause where sargability can matter; it can also have an effect on ORDER BY, GROUP BY and HAVING clauses. The SELECT clause, on the other hand, can contain non-sargable expressions without adversely affecting the performance.

  • Sargable operators: =, >, <, >=, <=, BETWEEN, LIKE without leading %
  • Sargable operators that rarely improve performance: <>, IN, OR, NOT IN, NOT EXISTS, NOT LIKE
  • Non-sargable operators: LIKE with leading wildcards

Rules of thumb

  • Avoid functions using table values in an SQL WHERE condition.
  • Avoid non-sargable predicates and replace them with sargable equivalents.

Examples[edit]

Find date values in a certain year:

  • Non-sargable: SELECT ... WHERE EXTRACT(YEAR FROM date) = 2012
  • Sargable: SELECT ... WHERE date >= '2012-01-01' AND date < '2013-01-01'

Handling NULLs:

  • Non-sargable: SELECT ... WHERE COALESCE(FullName, 'John Smith') = 'John Smith'
  • Sargable: SELECT ... WHERE (FullName = 'John Smith') OR (FullName IS NULL)

String prefix search:

  • Non-sargable: SELECT ... WHERE SUBSTRING(DealerName FROM 1 FOR 6) = 'Toyota'
  • Sargable: SELECT ... WHERE DealerName LIKE 'Toyota%'

Find rows from last 20 days:

  • Non-sargable: SELECT ... WHERE EXTRACT(DAY FROM (CURRENT_DATE - date)) < 20
  • Sargable: SELECT ... WHERE date >= (CURRENT_DATE - INTERVAL '20' DAY)

References[edit]

See also[edit]