[thelist] database structure for speedier queries
Phil Turmel
pturmel-webdev at turmel.org
Sat Dec 1 15:06:30 CST 2007
Joel D Canfield wrote:
>>sounds like adding the archived field and setting it to null
>>or not null is the best solution.
>
>
> question to the other folks more db-savvy than I - what's the difference
> in current use or future-proofing between using NULL or not, versus
> using 0 or 1?
>
> eddicate me
>
> thanks
>
> joel
Hi Joel,
Strictly speaking, NULL is not equivalent to zero, nor equivalent
to FALSE. That introduces some complications, especially the
unreliability of testing its boolean "value" with anything other
than "IS NULL" or "IS NOT NULL". The MySQL manual has an
appendix on the pitfalls[1], though it's not specific to MySQL.
However, if the data in question truly is optional, NULL is
entirely appropriate for missing entries. This can save storage
space (eliminating another column to indicate valid data) or
eliminate ambiguity. NULL status for the columns of a row is
typically an internal packed-bit structure, while booleans are
almost always represented as individual 8-bit values. If you
have lots of optional values in a large dataset, that's likely to
be significant.
On some platforms, NULL values have the interesting property of
appearing first in a sort, regardless of ascending or descending
order.
Modern DBs have no problem with NULLs in indexes, though it used
to be at least a performance hit, or forbidden.
There are some cross platform differences, unfortunately. Oracle
converts and stores zero-length strings as NULL[2], while most
other platforms do not[1,3].
HTH,
Phil Turmel
[1] http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html
[2]
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref536
[3] http://msdn2.microsoft.com/en-us/library/ms191504.aspx
--
Need to contact me offlist?
Drop -webdev or you probably won't get through.
More information about the thelist
mailing list