Geek Answers Handbook

Performance difference between two similar sql queries

What is the difference between execution:

SELECT * FROM table WHERE column IS NULL

or -

 SELECT * FROM table WHERE column = 0

Does IS NULL significantly worse than equating to a constant?

A use case appears where I have something like:

 SELECT * FROM users WHERE paying IS NULL

(or adding an extra column)

 SELECT * FROM users WHERE is_paying = 0
+4
source share
4 answers

If I understand your question correctly, you are asking about the relative advantages / problems with two situations:

  • where is_paying = 0
  • where payment is null

Given that both are in the data table, I cannot think of why one could work better than the others. I really think that in the first place it’s clear what the request is doing, so this is the option I would prefer. But in terms of performance, they should be the same.

Someone else mentioned - and I'm sure you know that NULL and 0 are different animals. They can also behave differently in optimizing joins and other elements. But for simple filtering, I would expect them to have the same performance.

Well, there is one technical responsibility. Comparison with "0" is probably built into the CPU. Comparing with NULL is probably a small operation requiring something like a mask, offset, and comparison — which can take longer. However, this performance difference is negligible compared to the fact that you are reading data from disk to begin with.

+1
source

compared to NULL and zero are two different things. zero is the value (known value), and NULL UNKNOWN . zero means the value was zero ; NULL means that the value was not set or was set to null.

0
source

As far as I know, comparing to NULL is as fast as comparing to 0, so you should choose based on:

  • Simplicity - Use an option that simplifies your code.
  • Minimum size - use a parameter that makes your table smaller

In this case, creating a paying NULL -able column would probably be better.

You should also check these questions:
NULL in MySQL (performance and storage)
MySQL: NULL vs ""

-1
source

You will get completely different results using these queries; this is not just a performance issue.

Suppose you have many users. Some of them have non-zero values ​​for the "pay" column, some of them have 0, and some have no value. The last case is that “zero” more or less represents.

In terms of performance, do you have an index in the pay column? If you have only a few hundred rows in the table, that probably doesn't matter. If you have many thousands of rows, you basically say that the query iterates over each row of the table if you don't have indexing in place. This is true whether you are looking for "pay = 0" or "pay is null".

But then again, just to overestimate, the two queries will give you completely different results.

-1
source

More articles:


All Articles