Search code examples
mysqlmyisam

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

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?

Solution

  • 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.