Date: 05/11/05 (MySQL Communtiy)    Keywords: php, mysql, sql, web

I've got a typical weblog system setup, which allows users to comment upon posts.

The comments are located in their own table which has a structure similar to this (actually it stores thinks like IP address, etc too):

CREATE TABLE comments (
  id int(11) NOT NULL default '0',
  article int(11) NOT NULL default '0',
  parent int(11) NOT NULL default '0',
  title varchar(65) default NULL,
  author varchar(25) NOT NULL default '',
  body text,
  PRIMARY KEY  (id,article)
);

This is pretty standard stuff - the comments are associated with a particler "article", and they may also be replies to other comments - in which case the "parent" will point to the parent comment id.

This structure allows there to be a nice nested tree of comments, in which some are "top level" because they have no parent set, and others are nested.

One problem I'm having is that with this structure is that the naive implementation for showing the comments is using recursion - and this is slowing down significantly as the size of the comments grows.

The pseudocode looks something like this:

#  $article is the article these comments are associated with
#  $parent is the parent of the children we want to see
#  $level  is increased when we go deeper into the tree,
#          used to display a nice indented tree
sub display_children($article, $parent, $level) 
{
   # retrieve all children of $parent
   $result = mysql_query('SELECT * FROM comments WHERE article=$article AND parent= $parent') ;

   # display each child
   while ($row = mysql_fetch_array($result)) 
   {
       # indent and display the title of this child
       echo str_repeat('  ',$level).$row['title']."\n";

       # call this function again to display this
       # child's children
       display_children($article,$row['id'], $level+1);
   }
}

# Display comments for article 40:
display_children( 40, 0, 0 );

(This is a pretty faithful representation in PHP, but the actual code is Perl, using DBI).

So now onto the question:

How can I speed this up, and avoid the recursion?

I've looked at CPAN for DBI::Tree, and other obvious candidates but I see nothing suitable.

I think I probably need to change the structure into something more efficient to display, but I admit my SQL-fu is weak.

Any suggestions appreciated.

Source: http://www.livejournal.com/community/mysql/56692.html