[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