Part 2: Non-SARGable Query Conditions
Like most programmers, database developers tend to write code that is more or less a direct translation of a given request. The fact that most programming languages - SQL included - are designed to be human readable, also contributes to this problem. Why is this a concern? All programming languages execute certain operations faster than others. In relational databases, the query optimizer analyzes SQL queries and determines the efficient execution mechanisms called query plans. The optimizer generates one or more query plans for each query, each of which represent one possible way to run a query. The most efficient query plan is then selected and utilized to run the query. As it turns out, SQL that mimics the language of a request is seldom the most efficient approach.
In this installment of the Top SQL Query Mistakes series, we'll explore one example of a poorly written SQL statement and rewrite it in a way that increases efficiency.
Passing Indexed Columns to Functions
One faux pas that comes up over and over again in database developers' code is the passing of index columns to functions. To illustrate, let's execute a query against this table, which has an index on the varchar customerName column:
When asked to retrieve all customers whose name starts with the letter "R", one might be inclined to use the LEFT() function to return the first character of the customerName column:
Unfortunately, by passing the indexed customerName column to a function, the query engine must evaluate its result for every row in the table!
SARGable vs. Non-SARGable Queries
In relational databases, there is a term that is derived from a contraction of Search ARGument ABLE, aka, SARGable. 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. On the other side of the coin, a query that fails to be SARGable is known as a non-SARGable query. The effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index. Obviously, this has a negative effect on query time, so one of the steps in query optimization is to convert such conditions to be SARGable.
To make a condition such as the one above into a SARGable one, we need to avoid the use of functions on the indexed columns. To do that, we must express the request with this logically equivalent (and SARGable) query, using the Like operator:
Notice the greatly improved execution time.
Conclusion
In this second installment on Top SQL Query Mistakes, we learned how non-SARGable query conditions can degrade query performance by forcing the database engine to evaluate every row of a table. The fix is to express the request with a logically equivalent (and SARGable) condition that does not rely on a function call.
If you'd like to give Navicat 16 for MySQL a test drive, you can download a 14 day trial here.
