title: When to use NULL in MySQL tables
body: <p>I appreciate the semantic meaning of a NULL value in a database table, different from both false and the empty string ''.  However, I have often read about performance problems when fields are nullable and been advised to use an empty string in cases where NULL is actually semantically correct.  </p>
  
  <p>What circumstances are appropriate to use nullable fields and NULL values?  What are the trade-offs?  Is it sensible to simply avoid using NULLs altogether and simply use empty strings, false or 0 to indicate the absence of a value?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>OK - I understand the semantic difference between '' and NULL as well as the (performance-agnostic) circumstances in which NULL is the appropriate field value.  However, let me expand on the hinted performance issue.  This is from the excellent "High Performance MySQL" by Schwartz, Zeitsev et al 
  <a href="http://www.borders.co.uk/book/high-performance-mysql-optimization-backups-replication-and-more/857673/" rel="noreferrer">http://www.borders.co.uk/book/high-performance-mysql-optimization-backups-replication-and-more/857673/</a>:</p>
  
  <blockquote>
    <p>It's harder for MySQL to optimize
    queries that refer to nullable coumns,
    because they make indexes, index
    statistics, and value comparisons more
    complicated.  A nullable column uses
    more storage space and requires
    special processing inside MySQL.  When
    a nullable column is indexed, it
    requires an extra byte per entry and
    can even cause a fixed-size inded
    (such as an index on a single integer
    column) to be converted to a
    variable-sized one in MyISAM.</p>
  </blockquote>
  
  <p>More here:
  <a href="http://books.google.com/books?id=BL0NNoFPuAQC&amp;pg=PA81&amp;lpg=PA81&amp;dq=%22avoid+null+if+possible%22&amp;source=web&amp;ots=CNSMtvsC4R&amp;sig=Bbp2iSaTdzWpgYhOOcat3aKLuzI&amp;hl=en&amp;sa=X&amp;oi=book_result&amp;resnum=3&amp;ct=result" rel="noreferrer">Google books preview</a></p>
  
  <p>This is quite possibly the definitive answer - I was just looking for second opinions and experience from the front-line.</p>