Talk:Sargable

From Wikipedia, the free encyclopedia
Jump to: navigation, search
WikiProject Computing / Software  
WikiProject icon This article is within the scope of WikiProject Computing, a collaborative effort to improve the coverage of computers, computing, and information technology on Wikipedia. If you would like to participate, please visit the project page, where you can join the discussion and see a list of open tasks.
 ???  This article has not yet received a rating on the project's quality scale.
 ???  This article has not yet received a rating on the project's importance scale.
Taskforce icon
This article is supported by WikiProject Software.
 

Just stumbled upon this ... and the examples just don't make any sense. It's just some random selection of predicates that may or may not be possible to look up using an index with some specific version of some specific RDBMS, but there is nothing fundamentally preventing an RDBMS from using an index for looking up, say, LIKE '%foo'. If it's possible to create an index that can be used for looking up REVERSE(x) LIKE 'oof%', you obviously can create one for looking up x LIKE '%foo'.

Also, you obviously can create indices on any functional mapping of a record, such as Year(), isNull(), or SUBSTRING(). And more generally, if a human can do some algebraic transformation on a predicate, then so can an RDBMS, so for any two predicates of which one is sargable and the other can be algebraically transformed into the first, it follows that second one can in principle be looked up by the RDBMS using some kind of index as well, which covers all of the examples given. It's just a matter of which specific optimizations the respective RDBMS implements whether it will figure it out or not.

Also, the rule "functions in the left part of a sql condition." doesn't make any sense. There isn't any such thing as a "left part of a sql condition.", and in any case there shouldn't be any fundamental difference between sides of (commutative) relation operators with any not completely braindead RDBMS. booleancolumn doesn't have any sides and is a perfectly sensible predicate. func(column) = 'foo' and 'foo' = func(column) should result in exactly the same execution plan with even the most simple-minded optimizers. — Preceding unsigned comment added by 85.116.198.153 (talk) 03:03, 14 June 2012 (UTC)

<Real World Intrusion Alert>
RE: "... the rule "functions in the left part of a sql condition." doesn't make any sense.": This may be a collision between relational theory and actual experience with commercial databases, in particular with the query optimizers of commercial databases.
Sadly, query optimizers do indeed pay attention to rvalue vs. lvalue position in a WHERE statement. Check out http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/ and http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/ for SQL Server examples. Mind, I won't necessarily argue with the 'Brain-dead' characterization of query optimizers, especially at 3 AM. 216.26.134.251 (talk) 14:01, 28 August 2013 (UTC)OlGrizz
Those two pages you mentioned do not point out any difference between the left side and the right side of a comparison in a WHERE clause. They both point out differences between using functions and not using them. Razvan Socol (talk) 06:58, 2 September 2013 (UTC)

"IN" and "NOT IN" not sargable?[edit]

I am confused by why "IN" and "NOT IN" would not be sargable. Either the article is wrong, or the "IN" / "NOT IN" situation needs to be explained in more detail.

Isn't "col IN (1,2,3)" just syntax sugar for "col = 1 OR col = 2 OR col = 3"? One of the examples ("WHERE ((FullName = 'John Smith') OR (FullName IS NULL))") shows that the "OR" logical disjunction is sargable. 24.184.169.144 (talk) 01:29, 30 July 2012 (UTC)