Is there any speed difference in checking for field = '' vs field IS NULL?

Question

I have a query:

SELECT * FROM `apps` WHERE dev_name = '' ORDER BY RAND() LIMIT 10;

Right now I'm checking for a blank dev_name but would it be faster if I set the default value to NULL and used NULL in the SELECT instead?

Duplicate of http://stackoverflow.com/questions/1106258/mysql-null-vs — Robbert van den Bogerd, Dec 16 '15 at 16:02

score 0 · Answer 1 · answered Dec 16 '15 at 16:08

In general I would say this is a micro-optimization and it's better to model your queries and table structure to something that feels semantically correct.

As this is a generic question, just try to read up on other questions here on SO and decide for yourself. It's hard to say if your specific setup will have a performance win by any of the options. If you want to know for sure, try to make a benchmark for yourself, that's also fun to do :)

score 0 · Accepted Answer · answered Dec 16 '15 at 19:15

Speed is not the reason for picking one versus the other. In your limited example, x = '' and x IS NULL will probably preform identical speed.

What should be considered in NULL versus '' (or 0) is other forms of testing, and what the meaning of the column is.

For example, if there are "no fees", should the fee column be 0 or NULL? NULL seems to imply "no fees". However, WHERE fee < 100 is less clumsy than WHERE fee IS NULL OR fee < 100, so I would vote for 0.

COUNT(dev_name) will count '', but not NULL. This could be the deciding factor.

Etc.

Is there any speed difference in checking for field = '' vs field IS NULL?

2 Answers2