Question:
If an index is created in my table X with fields A, B and C (in that order), should I follow exactly that order in the SQL queries?
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 the 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 reading speed, would the two queries above have the same result?
Answer:
It depends on the database implementation (no mainstream that I know of, other than an unlikely bug ), mostly it doesn't matter, it will figure out how best to use the index, at least in the simplest 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 true for any query, there are situations where the order will influence whether or not it can use the index for performance.
Generally you must create the necessary indexes for the queries you use in the code and in fact it is proven that there are gains. Remembering that the creation of an index incurs an extra cost, mainly in writing, but it also affects reading because it has more data to "dirty" the cache.
The maxim that performance must be tested is always valid, a lot that is valid here is not valid there, in a database this is more true because even the volume and distribution of data affects whether it will have a good result or not.