7
If in my table X an index is created with the fields A, B and C (in this order), in the SQL queries I must follow exactly this 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 the order of the index:
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, the two consultations above would have the same result?
7
Depends on database implementation (none mainstream that I know of, except for an improbable bug), most of it doesn’t matter, it will find the best way to use the index, at least in the simplest cases like this. In more complex cases it may not occur the proper utilization of the index in the most naive Dbs. 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 to get performance.
Generally one should create the necessary indexes for the queries you use in the code and in fact be proven that there are gains. Remembering that creating an index incurs an extra cost, mainly of writing, but also affects reading by having more data to "mess up" the cache.
It is always worth the maximum that performance should be tested, much that is worth here is not worth there, in database this is more true because even the volume and distribution of the data affects whether it will have good result or not.
You said it depends on the database implementation and that most of it doesn’t matter. Would you be able to increase your response by showing which databases the order of the index affects the performance of the query? I was curious.
None that I know today, at least of which are used by many people.
Do you think you could test if you use the index through EXPLAIN? Then I would query both modes and see if you use the index.
@Clebergriff yes, it is a technique, for those who know how to interpret what it shows, although in a simple case like this, it should be easy because it should show the same.
5
No, the order in which you construct the expression of your WHERE does not influence performance (or should not).
Whichever RDBMS who is able to analyze the plan of the query that is running in order to determine the best way to filter the data.
The pattern ANSI SQL says the following:
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.
Thanks for the answer. You went straight to the point and said "no". In the other answer they said "it depends". I’m still in doubt.
@Electus The only way to know with absolute certainty whether it affects performance is (as obvious as it may seem) by performing performance tests. Depending on the query, data, software and even hardware, it can make a difference or not, but you will only know by testing, each case is a case. But before I go back one step: is this currently generating a performance problem? If it is not, I wouldn’t even worry about it, to begin with...
@Electus If it is for lack of NO: No, no and no. You can give this answer as the correct one. The Relational Database Management System (RDBMS) engine will not be affected by the order of the fields in the query, the order by, the order of the Inner joins, etc. It was built in order to abstract all these things p/you. Although in theory someone could build a RDBMS.
If you start studying how these systems work in practice you will see that this is not something to worry about (although you will find that there are thousands of other things to worry about)
Browser other questions tagged sql database performance índices
You are not signed in. Login or sign up in order to post.
In case it would have the same results in question of performance and results of the data, the order in that case would not matter.
– Nakamura
In theory, it doesn’t matter. In practice, it depends on how the engine that processes SQL was done. It is expected that the two queries are equivalent. To be sure, just by reading the engine documentation used. SQL is not processed as text, it usually goes through a query Planner that tries to optimize in the best possible way, so obvious situations are usually equivalent.
– Bacco