Does order of expressions in the WHERE clause matter?
Does the order of expressions in a WHERE clause matter for performance?
So an interesting point found in a Google Cloud blog post was the fact that this expression order matters, with BigQuery assuming that the user has provided the best order of expressions in the WHERE clause, so it would not reorder expressions.
The recommendation given is to put the most selective expression first - basically, the one that narrows down the result set the most.
For testing it out, I've picked a ~200 million rows table (the publicly available google_trends.international_top_terms) .
The two scenarios to be tested were the ones presented in the same blog post - an exact match on a string and a wildcard lookup, then switching their order in the WHERE clause.
Now, the blog post is already more than 2 years old and some things might have changed, given I wasn't able to replicate the results very well.
The results have shown almost no difference between the two approaches (across several attempts), but another reason might be the table is just not big enough for me to see a difference.
In any case, I'll keep this in mind next time I'm working with a very big table and check it out again.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.
Enjoyed this? Here are some related articles you might find useful:
