Does the order of parameters in the where clause affect whether a table uses an index?

Question

So I am wondering if there is a definitive answer to this question.
Also, does it matter if the index is clustered vs. non-clustered. Is it the same in all RDBMS implementations or is the exact behavior going to be proprietary?

Answer 1

SQL is a declarative language, not a procedural one. Each SQL implementation is going to have its own quirks about implementation details like which indexes get used, how the optimizer decides which indexes to use, what the SQL programmer can do to affect the choice, and so on.

Answer 2

Use of indexes is not a part of the SQL standard, but rather an implementation detail of the particular DBMS.

Ideally, it shouldn't affect it, since it doesn't affect the actual rows that are returned.

But I've seen queries on an unnamed DBMS that does change index use based on the SQL query order.

Answer 3

The ordering of the where clause shouldn't affect the query plan or the indices used in any respectable database although I have seen at least one (non-respectable) database where this wasn't the case.

Answer 4

At one time (long ago, i.e. until about 1995) Oracle used to have only a "rule based optimiser", and with this it was certainly the case that the order of predicates in the WHERE clause, and the order of tables in the FROM clause (there was no JOIN syntax then), affected the query plan: this was documented to be the case. However, cost-based optimisers (which Oracle has had since then) attempt to examine all possible plans (or at least, as many as they can within some sensible parameters) and choose the most efficient.

Answer 5

This is tough to answer because I think no one really knows, including the DBMS engineers! LOL, that is sarcasm, but what I really mean is that it is inherently non-deterministic. I could be wrong, but it really does boil down to the DB engine implementation, since the ANSI SQL standard, or any other standard does not to my knowledge regulate this notion of index semantics. I do know, however, that the FIRST reference to any indexed field matters, as it marks the top of the decision tree for the query engine. From there and depending on the number and type of indexes, the query engine may choose to use the first and most matching index it finds, or it may decide to "optimize" and instead use another index. That, I think, is the part that makes it non-deterministic.