title: Fast Relational method of storing tree data (for instance threaded comments on articles)
body: <p>I have a cms which stores comments against articles. These comments can be both threaded and non threaded. Although technically they are the same just with the reply column left blank when it's not threaded. My application works on sqlLite, MySQL and pgsql so I need fairly standard SQL.</p>
  
  <p>I currently have a comment table</p>
  
  <pre><code>comment_id
  article_id
  user_id
  comment
  timestamp
  thread (this is the reply column)
  </code></pre>
  
  <p>My question is to figure out how to best represent the threaded comments in the database.  Perhaps in a separate table that supports the tree set without the content and a simple table to hold the text? Perhaps in the way it already is? Perhaps another way?</p>
  
  <p>If the comments are un-threaded I can easily just order by the timestamp.</p>
  
  <p>If they are threaded I sort like this</p>
  
  <pre><code>ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))
  </code></pre>
  
  <p>As you can see from the ORDER BY, the commenting queries will not ever use an index as function based indexes only really live in Oracle.  Help me have lightening fast comment pages.</p>