CODEWITHSUNDEEP
mysqlsql
Evert
Evert

Reputation: 2182

Does MySql analyze / optimize queries before executing them?

If you'd write a query like so:

SELECT * FROM `posts` WHERE `views` > 200 OR `views` > 100

Would MySql analyze that query and realize that it's actually equivalent to this?

SELECT * FROM `posts` WHERE `views` > 100

In other words, would MySql optimize the query such that it skips any unnecessary WHERE checks?

I'm asking because I'm working on a piece of code that, for now, generates queries with redundant WHERE clauses. I'm wondering if I should optimize those queries before I send them to MySql, or if that's unnecessary, because MySql would do it anyway.

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

Yes. MySQL does optimize queries before running them. In fact, what runs has no obvious relationship to the SQL statement itself -- it is a directed acyclic graph.

In the process, MySQL determines what indexes to use for the query, what join algorithms, sorts lists of constants in in lists, and much more.

The optimizer also does some simplifications of the query. I'm not sure if those simplifications extend to inequalities. However, there is little overhead in making the comparison twice.

Upvotes: 2

Rick James
Rick James

Reputation: 142443

EXPLAIN SELECT ... Shows how the query was rewritten -- but it still has the OR.

The "Optimizer trace" says the same thing. However, when it gets into discussing the "cost", it gets smart and merges the two comparisons. (This is the case at least as far back as 5.6.)

In many cases, OR should be avoided like covid.

Upvotes: 0

Related Questions