("" OR null) VS NOT(!"" AND NOT NULL) in where clause

Question

Does anyone know if there is a performace differance between the following we parts of a where SQL string in mysql?

   WHERE ...  AND (field = "" OR field IS NULL);

and

  WHERE ...  AND (NOT (field != "" AND field IS NOT NULL));

Answer 1

There is but it is negligibly small, so small I can't tell which would be faster and which slower. It's more of a matter of which is more readable — in this case I would pick the former.

Answer 2

there is no performance difference as such,. its much more about code-readability here,. and of course the first statement is more readable,.

one more thing i would like to add is when thinking about optimizations,. one should not delve into micro-optimizations because that doesnt really add up to increasing performance because the difference is very negligible and u end up wasting time and resources and making your code less readable, less maintainable and less manageable.,

so whenever thinking about optimizations,. profile your code and start out with things like improving ur app design, db design, utilizing caching and so on,. rather then indulging into discussions about whether echo is faster or print (an example)

Answer 3

There is performance difference. In the way you have written query it depends upon the value of the variable "field".
In the query "WHERE ... AND (field = "" OR field IS NULL);" if field = "" is TRUE then field IS NULL will not be checked whether TRUE or FALSE. But if field = "" is FALSE then field IS NULL will be checked for TRUE or FALSE. Number of comparisons done depend on the value of "field".
In the query WHERE ... AND (NOT (field != "" AND field IS NOT NULL)); whether field != "" is TRUE or FALSE, field IS NOT NULL will be checked for TRUE or FALSE. Hence in either case comparison will be done twice. And finally NOT is checked. Hence overall three comparisons.
I prefer the former one, though the difference is negligible.
Its better not to use "NOT", although there will not be any significant performance difference considering today's compilers and system configurations.

Answer 4

The two are equivalent, and should be executed with exactly the same plan. If they are not then there is no guarantee that just because a) executes faster than b) today then they always will do.

The equivalence of the two is a direct application of elementary Boolean Algebra. If you have not had the opportunity already it's well worth your time to take a class or read a text covering it. The basics do not constitute a particularly large subject area, and the concepts are not particularly hard (you probably know much already, although probably not formally), but understanding the rules pays off richly once you have them under your thumb.

Answer 5

According to default behavior of NULLs in SQL server (at least MSSQL) is that:

VALUE Logical_Operator NULL == FALSE

,- that is any Boolean operator with NULL results in FALSE (except special operator "IS NULL"). So in your second expression

"AND field IS NOT NULL"

is redundant part, because

(NULL != "") == FALSE

Now back to your question. First expression should be slower, because when we request sql server to select rows with field which is NULL,- server can't use INDEX on that field, and uses plain table scan (look at execution plan). So because of this my main advice is that never use nulls in any query - better mark empty fields with pre-defined symbol or just empty string.

EDIT: I've seen 'jokes' on mssql that construct below on some cases would run faster than your first expression (and possibly the second):

WHERE ... AND COALESCE(field,"")=""

My best guess at explaining this is that sql server don't likes OR cases, because by looking at query execution plan (with OR case) it is noticeable that server splits query into several parts and after executing these parts - merges results of both OR part queries. In any case you can add this third candidate for your performance experiments.