Reducing query count in comments

[revenazb]

Hi all,

I was loooking through the code to try and improve the query count numbers even further than what has been accomplished in the latest SVN.

I figured there was some room for improvement in comments. As the code stood, for every comment a query would be run to see if it had any children. This seemed somewhat wasteful.

By adding a new field to the comment table called has_child and modyfing the insert and update queries on comments, it becomes possible to only run the query looking for children when there are some.

You can add the field to your database using (if you are using pligg as your table prefix)

ALTER TABLE `pligg_comments` ADD `has_child` TINYINT NOT NULL DEFAULT '0';

ALTER TABLE `pligg_comments` ADD INDEX ( `has_child` ) ;

All in all, three files need to be modified.
1. /libs/comment.php

Add has_child = 0; to the class definition

PHP Code:

class Comment {
    var $id = 0;
    var $randkey = 0;
    var $author = 0;
    var $link = 0;
    var $date = false;
    var $karma = 0;
    var $content = '';
    var $read = false;
    var $parent = 0;
    var $hideedit;
    var $votes = 0;
    var $has_child = 0; 


Add the following:

PHP Code:

$this->id = $db->insert_id;
            // Let's update the has child field if need be
            if ($comment_parent)
                {
                    $sql = "UPDATE " . table_comments . " set has_child = has_child + 1, comment_date = comment_date WHERE comment_id=$comment_parent";
                    $db->query($sql);
                } 


right after the following line in the store fucntion

PHP Code:

$db->query($sql); 


2. admin_comments.php

The "if admin deletes comment" ( line 107 approx) section should now be

PHP Code:

// if admin deletes comment
    if ($_GET['action'] == "bulkmod") {
        if(isset($_POST['submit'])) {
            $comment = array();
            foreach ($_POST["comment"] as $k => $v) {
                $comment[intval($k)] = $v;
            }
            foreach($comment as $key => $value) {
                if ($value == "discard") {
                    // Let's get the id of the parent post to the one being deleted
                    $sql_parent_id = "SELECT comment_parent FROM " . table_comments . " WHERE comment_id = ".$key;  
                    $parent_id = $db->get_row($sql_parent_id);
                    $db->query('DELETE FROM `' . table_comments . '` WHERE `comment_id` = "'.$key.'"');
                    $db->query('DELETE FROM `' . table_comments . '` WHERE `comment_parent` = "'.$key.'"');
                    // Let's update the has_child field of the parent post.
                    $sql_child_count = "UPDATE " . table_comments . " set has_child = has_child - 1, comment_date = comment_date WHERE `comment_id` = ".$parent_id->comment_parent;
                    $db->query($sql_child_count);
                }
            }

            header("Location: ".my_pligg_base."/admin_comments.php");
        }
    } 


3. story.php

Here we actually put in the filter. the get get_comments() function should be like below.

PHP Code:

function get_comments (){
    Global $db, $main_smarty, $current_user, $CommentOrder, $link;
    
    //Set comment order to 1 if it's not set in the admin panel
    if(!isset($CommentOrder)){$CommentOrder = 1;}
    If ($CommentOrder == 1){$CommentOrderBy = "comment_votes DESC, comment_date DESC";}
    If ($CommentOrder == 2){$CommentOrderBy = "comment_date DESC";}
    If ($CommentOrder == 3){$CommentOrderBy = "comment_votes DESC, comment_date ASC";}
    If ($CommentOrder == 4){$CommentOrderBy = "comment_date ASC";}
    
    // get all parent comments
  $comments = $db->get_results("SELECT comment_id, has_child FROM " . table_comments . " WHERE comment_link_id=$link->id and comment_parent = 0 ORDER BY " . $CommentOrderBy);
  if ($comments) {
    require_once(mnminclude.'comment.php');
    $comment = new Comment;
    foreach($comments as $comment_id) {
      $comment->id=$comment_id->comment_id;
      $comment->has_child=$comment_id->has_child;  
      $comment->read();
      $comment->print_summary($link);            
    
            // get all child comments
            if ($comment->has_child) { // only if we already know it has one child comment
                $comments2 = $db->get_col("SELECT comment_id FROM " . table_comments . " WHERE comment_parent=".$comment->id." ORDER BY " . $CommentOrderBy);
                if ($comments2) {
                    echo '<div style="margin-left:40px">';
                    require_once(mnminclude.'comment.php');
                    $comment2 = new Comment;
                    foreach($comments2 as $comment_id) {
                        $comment2->id=$comment_id;
                        $comment2->read();
                        $comment2->print_summary($link);
                    }
                    echo "</div>\n";
                }
            }
    
         }
  }
} 


If you are doing this on an existing installation the has_child column will be out of sync. You will need to update it using a query.

I figure this can cut down on quite a few database calls on story views, at the cost of an extra databsse call on comment submit and two on admin deletes.

Hope this helps,
Bert

[kbeeveer46]

Thanks for the help. I should be adding this to the SVN shortly. The only thing I am concerned about is upgrading. You mentioned this

You will need to update it using a query.

Can you elaborate a little more on what people will have to do if the devs were to put this code in the SVN?

[Simon]

I think he means that when you add the has_child field to the database, the value has to che changed from 0 for every parent coment on the site. Maybe you could take the comment_parent field (or whatever it is called in the db) and use the ids from that to update the has_child field?

[kbeeveer46]

Yes, I know that but I thought he may be able to give some insight on the SQL. The has_child field holds the number of children comments that particular comment has. So somehow in upgrade.php we're going to have to count the number of child comments for every parent comment and then change the has_child field for that parent comment to the number of comments we just counted. We have to somehow do that for every parent comment.

[MicroBerto]

I've beenon vacation so excuse me if this is late,

So somehow in upgrade.php we're going to have to count the number of child comments for every parent comment and then change the has_child field for that parent comment to the number of comments we just counted. We have to somehow do that for every parent comment.

Isn't this the exact query that's been done too often from the beginning:

As the code stood, for every comment a query would be run to see if it had any children. This seemed somewhat wasteful.

berto

[revenazb]

Hi all,

The main porblem in doing this update using a single query is that in mysql you cannot update a table and select from the same table in subquery. So we have to use a view (a temp table would work as well.

CREATE VIEW pligg_com_upgrade AS SELECT Count(comment_parent) as children, comment_parent FROM pligg_comments WHERE comment_parent !=0 GROUP BY comment_parent

UPDATE `pligg_comments` SET has_child = 0

UPDATE pligg_comments, pligg_com_upgrade SET
has_child = children
WHERE pligg_comments.comment_id = pligg_com_upgrade.comment_parent

DROP VIEW pligg_com_upgrade


That should do it, of course the table prefix has to be set properly

Bert