CODEWITHSUNDEEP
sqldatabasesqlitewhere-clause
thelolcat
thelolcat

Reputation: 11545

Does the order matter, if all columns are indexed?

example

  SELECT * FROM table WHERE column1 = x AND column2 = y

Assuming both column1 and column2 are indexed, does the order they appear in the WHERE clause matter?

one of the columns can exclude a higher number of records than the other. Should I put that first, or the column that excludes less records?

Upvotes: 1

Views: 154

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562398

No -- boolean expressions with AND are commutative. The optimizer should be able to figure out which column to use for each component of the index, regardless of what order you write your expression.

Your example is pretty straightforward, but this changes for lots of other cases:

  • if you use OR instead of AND
  • if you use > or != for range comparison instead of equality comparison
  • if you index column1,column2,column3 but only search on column2 and column3
  • other cases.

For more information, see my presentation How to Design Indexes, Really.

Upvotes: 1

Related Questions