Sargability.
I was watching a Youtube video about different methods of SQL query optimization, and the guy dropped this word- Sargability. What a word; lends itself quite well to a pirate accent! It’s a derivative of Sargable, which was coined in a paper from 1979 entitled "Access Path Selection in a Relational Database Management System". It’s primarily a mash-up of Search and Argument (SARG). Basically a query, or more accurately the predicate part of a query, is considered sargable if the predicate can be resolved by the storage engine by simply reading the information in the row without having to do extra calculations.
It sounds complicated but it’s fairly straightforward. Let’s consider a table representing users, that has columns for their names and ages. And let’s say AARP has contacted my company and wants to add people to their mailing list. I’m going to run a query along the lines of
SELECT * FROM Users WHERE age >= 65. This is a simple query with a sargable predicate, because the storage engine can simply grab the age entry for each row and pop in the equation, no extra steps necessary.
Ok, now AARP has changed their mind, and decided that they actually want everyone who will be eligible in ten years. They want to get a jump on informing potential customers about what they have to offer. If I’m not thinking about sargability, I might write a query like this
SELECT * FROM Users WHERE (age - 10) >= 65. Now the search engine has to perform an operation (albeit a simple one), on each age entry in the database. Obviously this is going to take more time than our first query. In this situation the same query can be made sargable by changing the predicate to WHERE age >= 55. It’ll get the same results, and allow the search engine to perform a simple comparison, taking place on the lowest level, with the least memory allocation.
This is just the tip of the query optimization iceberg. Once you ensure the predicate is sargable, there are all sorts of clever uses of indexes that will speed up your search times. But that's a story for another time.
Top comments (0)