List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 4 2000 4:25pm
Subject:Re: multiple ANDs in SELECT
View as plain text  
>>>>> "Benjamin" == Benjamin Pflugmann <philemon@stripped> writes:

Benjamin> Hi.
Benjamin> On Tue, Jan 04, 2000 at 03:58:08AM -0500, cyberjeff@stripped wrote:
>> I have a SELECT that has several "AND"s  .
>> 
>> Some of the AND's are on indexed fields, some are just looking at a flag
>> field, some are looking at fields that are cast...
>> 
>> Does the order of these ANDs in my select effect how fast the query
>> runs? 

Benjamin> No, the order is irrelevant. MySQL internally reorders them just as
Benjamin> its needs are.

>> How should they be ordered for the fastest queries? (Is this in the
>> manual and I missed it?)

Benjamin> I think there is nothing like this in the manual. But there is stuff
Benjamin> about general optimation issues, such as using indexes and so on.

Benjamin> If you get this part right, you should not worry about what MySQL does
Benjamin> behind the curtain until you actually hit a performance problem you
Benjamin> have to work around.

A short note about this.

For key optimization the order of the AND are irrelevant.  If you have
many ands, you should however put expressions that are likely to be
'not true' first as the WHERE clause can then be evaluated a bit
faster.  Benjamin is however right that in most cases this isn't
really that relelevant.

mysql> select benchmark(500000, 1=1 AND 2=2 AND 3=3 AND 4=0);
+------------------------------------------------+
| benchmark(500000, 1=1 AND 2=2 AND 3=3 AND 4=0) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (1.51 sec)

mysql> select benchmark(500000, 4=0 AND 1=1 AND 2=2 AND 3=3);
+------------------------------------------------+
| benchmark(500000, 4=0 AND 1=1 AND 2=2 AND 3=3) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.53 sec)

Regards,
Monty
Thread
multiple ANDs in SELECTJeff Thies4 Jan
  • Re: multiple ANDs in SELECTTonu Samuel4 Jan
  • Re: multiple ANDs in SELECTBenjamin Pflugmann4 Jan
    • Re: multiple ANDs in SELECTMichael Widenius4 Jan
  • Re: multiple ANDs in SELECTJeremy Cole4 Jan