Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?

For example, if I have a multi-column index on [:col_1, :col_2, :col_3], does my SELECT statement need to look like "SELECT * FROM my_table WHERE col_1 = (?), col_2 = (?), col_3 = (?)" in order for the query to be optimized? Or can I specify the params in any order and the query optimizer will handle it?

I've read a few similar answers, but there didn't seem to be a definitive answer that pointed to documentation, and answered relative to indexes. Some answers say "it shouldn't matter" or "effect is negligible".

I'm working with RoR/ActiveRecord on PostgreSQL, but the question is really for any SQL/relational DB.

share|improve this question

1 Answer 1

up vote 9 down vote accepted

No, you can specify the 'params' (the parts of the where clause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example.

You can't exactly prove this, but you can demonstrate it is true for a particular query by experimenting and seeing if the plan changes. It may even be true that there are edge cases where the order does matter, but my advice would be to ignore the possibility and assume it never happens as otherwise you will expend a lot of effort trying different permutations. Much better to focus on the kind of tuning which you know can pay dividends (eg correct indexing).

share|improve this answer
    
Ah, not a "definitive" answer, again! How disappointing. I fear we are not through with this yet and must expect more questions coming. :) Anyway, I really appreciate how such "indefinite" answers tend to confirm one another. So thanks from me and +1! –  Andriy M Feb 2 '13 at 14:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.