CODEWITHSUNDEEP
mysqlperformance
Alec
Alec

Reputation: 2154

Are multiple WHERE/AND clauses in MySQL checked sequentially? And are subsequent checks skipped if the first is false?

Scenario

Let's say I have a MySQL query that contains multiple WHERE/AND clauses.

For for instance, say I have the query

SELECT * FROM some_table
WHERE col1 = 5
AND col2 = 9
AND col3 LIKE '%string%'

Question

Is the col1 = 5 check done as the first in sequence here, since it's written first? And more importantly, are the other two checks skipped if col1 != 5?

The reason I ask is that the third clause, col3 LIKE '%string3%, will take more time to run, and I'm wondering if it makes sense to put it last, since I don't want to run it if one of the first two checks are false.

Upvotes: 1

Views: 89

Answers (2)

Rick James
Rick James

Reputation: 142298

The optimal index for that query is

INDEX(col1, col2)   -- in either order

Given that, it will definitely check both col1 and col2 simultaneously in order to whittle down the number of rows to a much smaller number. Hence the LIKE will happen only for the few rows that match both col1 and col2. This order of actions is very likely to be optimal.

Often, it is better (though not identical) to use FULLTEXT(col3) and have

WHERE col1 = 5
  AND col2 = 9
  AND MATCH(col3) AGAINST ("+string" IN BOOLEAN MODE)

In this case, I am pretty sure it will start with the FULLTEXT index to test col3, hoping to get very few rows to double check against the other clauses. Because of various other issues, this is optimal. Any index(es) on col1 and col2 will not be used.

The general statement (so far) is: The Optimizer will pick AND clause(s) that it can use for one INDEX first. In that sense, the order of the AND clauses is violated -- as an optimization.

If you don't have any suitable indexes, well, shame on you.

There are many possibilities. I will be happy to discuss individual queries, but it will be hard to make too many generalities.

Upvotes: 1

slaakso
slaakso

Reputation: 9070

The SQL optimizer looks at the query at whole and tries to determine the most optimal query plan for the query. The order of the contitions in where-clause does not matter.

Upvotes: 2

Related Questions