If in my table X is created an index with fields A, B and C (in this order), in SQL queries should I follow exactly that order?
Example following the order of the index:
SELECT * FROM X
WHERE X.A = VALOR_A
AND X.B = VALOR_B
AND X.C = VALOR_C
Example without following index order:
SELECT * FROM X
WHERE X.C = VALOR_C
AND X.A = VALOR_A
AND X.B = VALOR_B
In terms of performance and speed of reading, would the above two queries have the same result?
No, the order in which you construct the expression of your WHERE does not influence performance (or should not).
Any% of the% that you pay is able to analyze the plan of the query that is running in order to determine the best way to filter the data.
The RDBMS pattern reads as follows:
6.3.3.3 Rule evaluation order
[...]
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
It depends on the implementation of the database (no mainstream I know, except for an improbable bug ), mostly it does not matter, it will find the best way to use the index, at least in simpler cases like this. In more complex cases the proper use of the index in the most naive DBs may not occur. It is true that "order" in more complex cases is already a complicated concept to define.
This is not valid for any query, there are situations that the order will influence whether or not it can use the index for performance.
Generally you should create the necessary indexes for the queries that you use in the code and in fact it is proven that there are gains. Remembering that creating an index incurs an extra cost, especially for writing, but it also affects reading by having more data to "dirty" the cache.
It's always worth the maximum that performance should be tested, a lot of it's worth here is not worth it, in database this is more true because even the volume and distribution of data affects whether it will work well or not. >