Efficiency Issue

[aplusc]

Let's say I have a simple table with two columns - C1, and C2. C1 is indexed, while C2 is not. If I were to write a stamenet like this:

select C1, C2 from table WHERE C1 = "A" and C2 = "B"

Would it be any different if I write it like this:

select C1, C2 from table WHERE C2 = "B" and C1 = "A"

I theory, since C1 is indexed, the first statement would execute faster. The second condition (C2 = "B&quot should not even be evaluated in cases where C1 = "A" is false.

Now, my question is:

Does the order of conditions in the WHERE clause matter?

Or, is the server "smart" enough to know which fields are indexed and will perform those comparisons first (and not do other comparisons where applicable)?

Or, will the server perform ALL of the comparisons in the where clause, regardless of their order and whether they are indexed or not?

I'm using Sybase ASE 11, but I'm also interested in how this works in other systems.

 

[fluteplr]

Most if not all SQL servers worthy of the name will optimize your query for you. so the order of your where clause is not relevant to the speed of your query.

In the case you describe the obvious optimization would be to find all the records where c1 = 'A' using the index and then scan those to find the records where c2 = 'b'

Obviously other things come into play. For example if the table's data is 99% 'A's in C1 the optimizer probably knows that from stored statistics and will then scan c2 for 'B' first.

Also in sql server 2000 for example the smallest chunk of data that will be read from a table is 8k. So any table with 8k or less of data, basically the indexes are never used in a query since it is faster to just read the entire table.

 

[angiole]

Also, keep in mind that the vendor should provide an execution PLAN utility, that will display, even cryptically, what if any indexes are used in a query and when.

Cheers AA 8~)