Order in compote index & performance [ WAS: OR, IN & index usage]

[Andy Leung]

At 05:00 PM 5/5/00 +0300, sinisa@stripped wrote:
>
>Hi!
>
>Yes, index would be used, provided your table is large enough and
>expressions do not cover large part of the table.
>
>The order of elements is irrelevant, as server rearranges them anyway.

Hi,

I understand that the order of the conditions in the WHERE clause of a query
is irrelevant as the server rearranges them. But I was talking about the
order of the elements in a composite index, and surely the server would not
rearrange that order!

What I was asking was whether that order in a composite index has any effect
on performance. For example say I have a query:

SELECT *
FROM table
WHERE col_1 > "A"
  AND col_2 IN ("X", "Y", "Z")

with an index on (col_1, col_2)

Now suppose 90% of the rows have col_1 > "A" while only 5% of the rows have
col_2 IN ("X", "Y", "Z"). I don't know how exactly the index B-tree is being
searched, but I was wondering would changing the order of the index so that
the more "discriminating" column (col_2) comes first would make the query
faster. That is:

SELECT *
FROM table
WHERE col_2 IN ("X", "Y", "Z")
  AND col_1 > "A"

with an index on (col_2, col_1)

Would the above scenerio be any faster?

Thanks a lot!

Andy

>
>
>Regards,
>
>Sinisa