List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:March 19 2014 3:36pm
Subject:Re: Precedence in WHERE clauses.
View as plain text  
Am 19.03.2014 16:27, schrieb Morgan Tocker:
> Reindl,

you can call me Harald

>>> The order of the WHERE clause does not matter
>>
>> that is simply not true
>>
>> otherwise that documentation would not exist
>> http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
> 
> Yes, the order of the columns in composite indexes *does matter*.  This is a
> different question to what Christophe asked.

the question was "is there any performance impact"
and (DWProcessed / Lowest cardinality and indexed)

so *yes* it does matter, i have seen MySQL more than once not using any
index because a unlucky order of where clauses up to copy a 16 GB
table in a table file instead using a existing primary key
_______________________________

SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate < DATE_SUB(NOW(), INTERVAL 24
HOUR);

I'd like the first filter (DWProcessed / Lowest cardinality and indexed)

Is there any performance impact on query processing, about the order of
WHERE clauses ?


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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