What Does Sargable Mean?

“That query’s not sargable” says one developer to another.

“I know it’s not sargable… would you love it more if it had sargability?” replies the other developer.

“What does sargable mean?” you wonder in silence.

Understanding Sargability

OK, let’s look at this strange term “sargable” and “sargability”.

The term “sargable” is derived from the combination of “Search ARGument ABLE”. Some people therefore write it as SARGable (but I don’t often see SARGABLE – all uppercase). Sargability (or SARGability) is just the term we use when referring to the sargable ability of the query. Basically, whether or not it’s sargable.

Anyway, it’s widely accepted among SQL professionals that sargability refers to the ability of a database engine to efficiently use indexes to satisfy a query’s search conditions. When a query is sargable, the database engine can leverage indexes to filter rows, which can significantly speed up query execution.

In contrast, non-sargable queries force the database engine to perform a full table scan or a non-optimal index scan, where each row in the table or index is examined, leading to slower performance. Ensuring our queries are sargable is a good technique for optimizing database performance, especially when dealing with large datasets.

However, it’s believed that the term sargable was first introduced in a 1979 paper called “Access Path Selection in a Relational Database Management System” by P. Griffiths Selinger et al., which doesn’t limit sargable queries to the ones in the above definition (i.e. just those that can efficiently use an index). In one part it specifies:

A sargable predicate is one of form (or which can be put into the form) “column comparison-operator value”. SARGS are expressed as a boolean expression of such predicates in disjunctive normal form.

So it’s worth bearing that in mind when reading this article, which refers to the ability of a database engine to efficiently use an index. And just because a query is sargable, doesn’t mean that it will always use an index, or that there’s even an index available for it to use. That said, the definition of a sargable predicate outlined in that paper still applies, even if our discussion is based heavily around index usage. As some say if you can’t index it, it’s probably not SARGable.

But let’s not get too bogged down in the details. The general idea is that sargable queries are much better at allowing the database engine to hone in on only those rows that the query needs, and ignore those that are irrelevant. For example, if your database engine needs to scan 4,000,000 rows just to return 1 row, then there’s a good chance your query isn’t sargable.

Also, different RDBMSs have different capabilities and terminologies around indexing and query plans, so it’s worth bearing that in mind too.

Characteristics of Sargable Queries

For a query to be considered sargable, it must allow the database engine to use an index effectively. The following conditions typically make a query sargable:

Indexed Columns: The search condition involves columns that have indexes (or that could be indexed for that query).
Equality and Range Conditions: Comparisons using operators like =, >, <, >=, and <= are usually sargable.
Logical AND Conditions: Multiple conditions connected with AND are typically sargable if each condition is sargable.
Starts With or LIKE Patterns: Queries using LIKE 'pattern%' can be sargable because the index can efficiently match the beginning of the string.

Non-sargable queries often involve operations that prevent the efficient use of indexes, such as:

Functions on Indexed Columns: Using functions like UPPER(), LOWER(), DATE(), or mathematical operations on indexed columns can make a query non-sargable.
Wildcards at the Start of LIKE Patterns: Queries using LIKE '%pattern' are non-sargable because the index cannot be used to search from the end of the string.
OR Conditions: Combining conditions with OR often leads to non-sargable queries, especially if some of the conditions are non-sargable.

Example of a Sargable Query

Let’s consider a simple example with a table named Employees that has the following columns: EmployeeID, FirstName, LastName, and HireDate. Assume that an index is created on the HireDate column.

Sargable Query:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate >= '2020-01-01';

In this query, the WHERE clause uses a range condition on the HireDate column. Since HireDate is indexed, the database engine can use this index to quickly find the relevant rows, making this query sargable.

Non-Sargable Query:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE YEAR(HireDate) = 2020;

Here, the YEAR() function is applied to the HireDate column. This function prevents the database engine from using the index on HireDate, resulting in a full table scan, which makes the query non-sargable.

To optimize the non-sargable query, we could rewrite it as:

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate >= '2020-01-01' AND HireDate < '2021-01-01';

This revised query restores sargability by allowing the database engine to use the index on HireDate.

How to Test for Sargability

To test whether a query is sargable, we can check the query plan. The query plan provides details about how the database engine executes the query, including whether an index is used.

The way you access the query plan will depend on your DBMS. For example, in MySQL we can use the EXPLAIN or EXPLAIN PLAN command in SQL. In SQL Server we can use SET SHOWPLAN_ALL ON/OFF.

Many GUIs have toolbar options such as Estimated Plan, Enable Actual Plan or similar. This can provide us with a graphical representation of the query plan, which can be easier to decipher.

MySQL Example

Let’s look at an example that uses MySQL’s EXPLAIN command:

EXPLAIN SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate >= '2020-01-01';

The output of this command will show if the index on HireDate is being used. If the key column in the result indicates that an index is used, the query is sargable.

Here’s a script that we can run to test this:

-- Step 1: Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

-- Step 2: Insert sample data into the Employees table
INSERT INTO Employees (FirstName, LastName, HireDate) VALUES
('Homell', 'Flack', '2019-05-15'),
('Bruce', 'Talcum', '2020-03-22'),
('Robert', 'Windly', '2021-07-19'),
('Susan', 'Affleck', '2018-10-01'),
('Michael', 'Johnson', '2020-06-30'),
('Sarah', 'James', '2022-01-11'),
('Cathy', 'Taylor', '2019-11-25'),
('Jessica', 'Howzitt', '2020-12-05'),
('Dave', 'Thomas', '2020-09-12'),
('Teddy', 'Bear', '2021-04-15');

-- Step 3: Create an index on the HireDate column
CREATE INDEX idx_HireDate ON Employees(HireDate);

-- Step 4: Test the Sargable query and view the execution plan
EXPLAIN SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate >= '2020-01-01';

-- Step 5: Test the Non-Sargable query and view the execution plan
EXPLAIN SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE YEAR(HireDate) = 2020;

-- Step 6: Test the modified Non-Sargable query (which should now be sargable) and view the execution plan
EXPLAIN SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate >= '2020-01-01' AND HireDate < '2021-01-01';

Output:

+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | Employees | NULL       | range | idx_HireDate  | idx_HireDate | 4       | NULL |    7 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | Employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | Employees | NULL       | range | idx_HireDate  | idx_HireDate | 4       | NULL |    4 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

In the EXPLAIN output, look at the key column:

If the key column shows the index name idx_HireDate, it means the query is sargable and the index is being used.
If the key column is NULL, it means the query is non-sargable, leading to a full table scan.

Therefore, we can see in the above output that the first and last queries are sargable (they used the index), but the second query is not sargable (it didn’t use the index).

As mentioned, I ran that script in MySQL. Other RDBMSs may use a slightly different syntax and terminology. Also, if using the GUI, look for an option that reads something like Estimated Plan or Enable Actual Plan. Options such as these can also provide a graphical representation of the query plan.

SQL Server Example

Here’s an example of two SQL Server query plans that I generated when using Azure Data Studio. Actually, I was running Azure SQL Edge (which uses the SQL Server database engine).

Sargable Query:

SELECT COUNT(ProductName) 
FROM SalesSummary WITH (NOEXPAND) 
WHERE ProductName LIKE 'Beer%';

Query plan:

This query uses an index seek, which is SQL Server’s way of saying that the query made full, efficient use of the index. It was able to do this because of the selective search predicate.

Non-Sargable Query:

SELECT COUNT(ProductName) 
FROM SalesSummary WITH (NOEXPAND) 
WHERE ProductName LIKE '%Beer%';

Query plan:

This query used an index scan which, while often better than a full table scan, is SQL Server’s way of saying that it had to scan all rows in the index to process the query, which is not necessarily the most efficient use of the index. So, just because a query uses an index, it doesn’t mean that it’s able to use it in the most efficient way (such is the case here). Therefore, we can probably call this query non-sargable.

By the way, the SQL Server examples here use indexed views (the indexes are on the views, rather than the underlying tables). In this case SalesSummary is a view that has had indexes applied to it.

Also, database engines may not always incorporate an index, even when we think it will. It all depends on whether the database engine thinks it can benefit from the index on that particular query. And even if it does use the index, it might not be able to use it efficiently (e.g. it may have to do a full scan rather than a seek or more selective scan). And sometimes if the tables are small, it might choose against using the index altogether. So this is something to be aware of.

Index Scan vs Index Seek vs …

Different RDBMSs use different terminology when referring to the utilization of indexes. For example, SQL Server distinguishes between index scan and index seek (with a seek being better than a scan).

Other RDBMSs have their own specific terms, such as Bitmap Index Scan, Index Scan, Sequential Scan, Index Range Scan, Full Index Scan, Index Unique Scan, etc.

Regardless of the term used, a sargable query will generally be able to take full advantage of an index.

Why Sargability Matters

Sargability directly impacts the performance of SQL queries. In large databases, non-sargable queries can lead to significant performance bottlenecks, as full table scans are much slower than index seeks (depending on which RDBMS terminology you use). By writing sargable queries, we can:

Improve Query Performance: Sargable queries execute faster, especially in large datasets.
Reduce Resource Usage: When a query utilizes an index efficiently, it will usually consume fewer CPU and I/O resources compared to full table scans.
Enhance Scalability: Efficient queries allow our applications to handle more users and larger datasets without degrading performance.

Conclusion

Understanding and applying the concept of sargability is crucial for optimizing SQL queries and improving database performance. By ensuring our queries are sargable, we can significantly reduce execution times and resource consumption, leading to faster and more efficient database operations.

Database.Guide