OR vs AND in MySQL
I'm using mysql and I can query in both ways (or / and) That means I can write query as: select * from x where () or () or () . . .. ; or I can write as: select * from x where () and () and () . . .. ;
Which is preferable? Is there any pe开发者_如何转开发rformance issue?
Forget about performance and what's "preferable", you need to use or
, and
depending on what you want from your query. This is basic boolean logic using truth tables. select * from x where () or () or ()
will be very different from select * from x where () and () and ()
.
How could this be a performance thing - as both queries work differently?
If you have where () or () or ()
; results are returned if ANY of the conditions is met
And if you have where () and () and ()
; results are returned only if ALL of the conditions are met.
Your sample predicates are not semantically equivalent so I wonder if you've oversimplified or forgotten to mention negation? e.g. something more like this:
WHERE (NOT col1 = 'T') OR (NOT col2 = 'T') OR (NOT col3 = 'T');
is the same as
WHERE NOT (col1 = 'T' AND col2 = 'T' AND col3 = 'T');
If so, I think that what @darioo said in comments is the answer i.e. that the engine will interpret the correct boolean logic as it sees fit and probably isn't worth worrying about. Your job is to ensure that your candidate constructs that you will be performance testing are indeed semantically equivalent :)
精彩评论