List:General Discussion« Previous MessageNext Message »
From:Morgan Tocker Date:March 19 2014 2:05pm
Subject:Re: Precedence in WHERE clauses.
View as plain text  
Hi Christophe,

> Considering the following simple query :
> 
> SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
> ('PENDING', 'ACCEPTED') AND SubscribeDate < DATE_SUB(NOW(), INTERVAL 24
> HOUR);
> 
> Which of these filters are processed first ?
> 
> I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
> being processed first, but I can't really find any useful information
> about this .
> 
> Is there any performance impact on query processing, about the order of
> WHERE clauses ?


When a MySQL server receives a query, it goes through a process called query optimization
and tries to determine the best way to execute it (based on availability of indexes etc).
 You can think of this as similar to how GPS software picks the fastest route - it is very
similar.

The order of the WHERE clause does not matter, and in fact more complicated
transformations happen in query optimization automatically.  For Example:
SELECT * FROM Status WHERE 1=1 AND DWProcessed = 0;

1=1 is detected as a tautology and removed.
Thread
Precedence in WHERE clauses.Christophe18 Mar 2014
  • Re: Precedence in WHERE clauses.Michael Dykman18 Mar 2014
    • Re: Precedence in WHERE clauses.Christophe20 Mar 2014
      • Re: Precedence in WHERE clauses.Roy Lyseng20 Mar 2014
  • Re: Precedence in WHERE clauses.Glyn Astill19 Mar 2014
  • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
    • Re: Precedence in WHERE clauses.Reindl Harald19 Mar 2014
      • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
        • Re: Precedence in WHERE clauses.Reindl Harald19 Mar 2014
          • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
            • Re: Precedence in WHERE clauses.Christophe20 Mar 2014
              • Re: Precedence in WHERE clauses.Christophe23 Mar 2014
                • Re: Precedence in WHERE clauses.shawn l.green25 Mar 2014
    • Re: Precedence in WHERE clauses.Mogens Melander19 Mar 2014
      • Re: Precedence in WHERE clauses.shawn l.green20 Mar 2014