[pLog-svn] r3234 - plog/trunk/class/summary/dao

Oscar Renalias oscar at renalias.net
Tue May 2 06:58:06 GMT 2006


Yeah it was taxing my sql skills too :) The query isn't mine either...

In fact I am not even sure whether we need this sort of feature. Would
anyone ever miss it if we remove it? If we keep it, we should consider
creating a new column called "activity_ratio" or something like that
in the blogs table, it will be much faster than doing the calculations
at the mysql level (and faster than GROUP BYs too!)

Oscar

On 5/2/06, Jon Daley <plogworld at jon.limedaley.com> wrote:
>         Does this actually work on your system?  I get sql errors, due to
> a missing GROUP BY on lin 216.
>         I am actually not sure what this query is supposed to be doing -
> or rather, why it is calculating it the way it is.  As an INNER JOIN
> better than a two table query?  After I add the GROUP BY a.id back in, I
> get 4 rows returned for my install, and I only have one blog.  Perhaps it
> should be a GROUP BY b.id, but I am not sure of the initial intent for the
> "most active blog" calculation - some sort of calculation based on how
> many posts and how often they are read tempered by how old the post is.
> Taxing my sql skills.
>
>
> On Tue, 11 Apr 2006, oscar at devel.lifetype.net wrote:
>
> > Author: oscar
> > Date: 2006-04-11 21:39:07 +0000 (Tue, 11 Apr 2006)
> > New Revision: 3234
> >
> > Modified:
> >   plog/trunk/class/summary/dao/summarystats.class.php
> > Log:
> > Some query optimizations. Removed a few (apparently) unnecessary GROUP BY operations and made some slight changes to a few queries
> > based on the output of the EXPLAIN command in the mysql client... It seems to have made a difference, at least in a small database like mine, the amount of rows scanned for each one of these queries went from the whole table to just a few but I'd like to see these in real life.
> >
> >
> > Modified: plog/trunk/class/summary/dao/summarystats.class.php
> > ===================================================================
> > --- plog/trunk/class/summary/dao/summarystats.class.php       2006-04-11 16:06:25 UTC (rev 3233)
> > +++ plog/trunk/class/summary/dao/summarystats.class.php       2006-04-11 21:39:07 UTC (rev 3234)
> > @@ -39,10 +39,18 @@
> >
> >             // calculate the date limits
> >             $t = new Timestamp();
> > -            $this->_now = $t->getTimestamp();
> > +            //$this->_now = $t->getTimestamp();
> > +                     $this->_now = $t->getYear().$t->getMonth();
> > +                     if( $t->getDay() < 10 )
> > +                             $this->_now .= "0";
> > +                     $this->_now .= $t->getDay();
> >             // 7 days ago
> >             $t->subtractSeconds( 7 * 24 * 60 * 60 );
> > -            $this->_sevenDaysAgo = $t->getTimestamp();
> > +            //$this->_sevenDaysAgo = $t->getTimestamp();
> > +                     $this->_sevenDaysAgo = $t->getYear().$t->getMonth();
> > +                     if( $t->getDay() < 10 )
> > +                             $this->_sevenDaysAgo .= "0";
> > +                     $this->_sevenDaysAgo .= $t->getDay();
> >
> >             // get the summary_page_show_max from config
> >             $config =& Config::getConfig();
> > @@ -60,28 +68,24 @@
> >             include_once( PLOG_CLASS_PATH."class/dao/articles.class.php" );
> >             include_once( PLOG_CLASS_PATH."class/dao/commentscommon.class.php" );
> >                       $articles = new Articles();
> > +
> > +            $maxPosts > 0 ? $max = $maxPosts : $max = $this->_summaryPageShowMax;
> >
> > -                     $prefix = $this->getPrefix();
> > -                     $query = " SELECT COUNT(*) as total_comments, a.*
> > -                                        FROM {$prefix}articles_comments AS c,
> > -                                             {$prefix}articles AS a,
> > -                                             {$prefix}blogs b
> > -                                        WHERE c.article_id = a.id
> > -                                              AND a.status = ".POST_STATUS_PUBLISHED."
> > -                                              AND c.status = ".COMMENT_STATUS_NONSPAM."
> > -                                              AND c.type = ".COMMENT_TYPE_COMMENT."
> > -                                              AND b.id = a.blog_id
> > -                                              AND b.status = ".BLOG_STATUS_ACTIVE."
> > -                                              AND a.date <= ".$this->_now."
> > -                                                      AND a.in_summary_page = '1'";
> > +                     $prefix = $this->getPrefix();
> > +                     $query = "SELECT a.*
> > +                                       FROM {$prefix}articles AS a,
> > +                                            {$prefix}blogs b
> > +                                       WHERE a.blog_id = b.id
> > +                                             AND a.status = ".POST_STATUS_PUBLISHED."
> > +                                                     AND b.status = ".BLOG_STATUS_ACTIVE."
> > +                                                     AND a.date <= ".$this->_now."
> > +                                                     AND a.in_summary_page = '1'
> > +                                                     AND a.num_nonspam_comments > 0
> > +                                       ORDER BY a.num_nonspam_comments DESC
> > +                                       LIMIT 0, $max";
> >
> > -                     $query .= " GROUP BY c.article_id ORDER BY total_comments DESC ";
> > +                     $this->log->debug("SummaryStats::getMostCommentedArticles query = ".$query);
> >
> > -            if( $maxPosts > 0 )
> > -             $query .= " LIMIT 0,".$maxPosts;
> > -            else
> > -             $query .= " LIMIT 0,".$this->_summaryPageShowMax;
> > -
> >             $result = $this->Execute( $query );
> >
> >             if( !$result ){
> > @@ -103,7 +107,7 @@
> >          *
> >          * @param maxPosts The maximum number of posts to return
> >          * @return an array of Article objects with information about the posts
> > -         * TODO: perfalmence tuning
> > +         * TODO: performance tuning
> >          */
> >         function getMostReadArticles( $maxPosts = 0 )
> >         {
> > @@ -115,16 +119,18 @@
> >              $query = "SELECT a.*
> >                  FROM {$prefix}articles a, {$prefix}blogs b
> >                  WHERE a.status = ".POST_STATUS_PUBLISHED."
> > -                 AND a.blog_id = b.id AND b.status = ".BLOG_STATUS_ACTIVE."
> > +                 AND a.blog_id = b.id
> > +                              AND b.status = ".BLOG_STATUS_ACTIVE."
> >                  AND a.date <= ".$this->_now." AND a.date > ".$this->_sevenDaysAgo."
> > -                              AND in_summary_page = '1'";
> > +                              AND in_summary_page = '1'
> > +                              ORDER BY a.num_reads DESC";
> >
> > -                     $query .= " ORDER BY a.num_reads DESC ";
> > -
> >             if( $maxPosts > 0 )
> >               $query .= " LIMIT 0,".$maxPosts;
> >             else
> >               $query .= " LIMIT 0,".$this->_summaryPageShowMax;
> > +
> > +                     $this->log->debug("SummaryStats::getMostReadArticles query = ".$query);
> >
> >             $result = $this->Execute( $query );
> >
> > @@ -159,6 +165,8 @@
> >               $query .= " LIMIT 0,".$maxBlogs;
> >             else
> >               $query .= " LIMIT 0,".$this->_summaryPageShowMax;
> > +
> > +                     $this->log->debug("SummaryStats::getRecentBlogs query = ".$query);
> >
> >             $result = $this->Execute( $query );
> >
> > @@ -191,17 +199,19 @@
> >
> >                       $prefix = $this->getPrefix();
> >             $query = "SELECT COUNT(*) as t, SUM((num_reads / (TO_DAYS(NOW()) - TO_DAYS(a.date) + 1)) ) as rank, b.*
> > -                       FROM {$prefix}articles AS a
> > -                       INNER JOIN {$prefix}blogs AS b
> > -                           ON b.id=a.blog_id AND b.status=".BLOG_STATUS_ACTIVE.
> > -                       " WHERE a.date >= ".$this->_sevenDaysAgo." AND a.date <= ".$this->_now."
> > -                                        AND in_summary_page = '1'
> > -                       GROUP BY a.blog_id ORDER BY rank DESC ";
> > +                      FROM {$prefix}articles AS a
> > +                      INNER JOIN {$prefix}blogs AS b
> > +                      ON b.id = a.blog_id AND b.status = ".BLOG_STATUS_ACTIVE."
> > +                      WHERE a.date >= ".$this->_sevenDaysAgo." AND a.date <= ".$this->_now."
> > +                                       AND in_summary_page = '1'
> > +                      ORDER BY rank DESC";
> >
> >             if( $maxBlogs > 0 )
> >               $query .= " LIMIT 0,".$maxBlogs;
> >             else
> >               $query .= " LIMIT 0,".$this->_summaryPageShowMax;
> > +
> > +                     $this->log->debug("SummaryStats::getMostActiveBlogs query = ".$query);
> >
> >             $result = $this->Execute( $query );
> >
> > @@ -250,12 +260,18 @@
> >                       if($globaArticleCategoryId != ALL_GLOBAL_ARTICLE_CATEGORIES)
> >                               $query .= " AND a.global_category_id = '".Db::qstr($globaArticleCategoryId)."'";
> >
> > -                     $query .= " GROUP BY a.id ORDER BY a.date DESC";
> > +                     //$query .= " GROUP BY a.id ORDER BY a.date DESC";
> > +                     /**
> > +                      * :TODO:
> > +                      * do we really need the GROUP BY? Why? MySQL does a better job without..
> > +                      */
> > +                     $query .= " ORDER BY a.date DESC";
> >
> >             if( $maxPosts <= 0 )
> > -             $maxPosts = $this->_summaryPageShowMax;
> > -
> > +             $maxPosts = $this->_summaryPageShowMax;
> >             $query .= " LIMIT 0,".$maxPosts;
> > +
> > +                     $this->log->debug("SummaryStats::getRecentArticles query = ".$query);
> >
> >             $result = $this->Execute( $query );
> >
> > @@ -317,7 +333,7 @@
> >                 array_push( $posts, $articles->getArticle($row["id"]) );
> >             }
> >
> > -            $result->Close();
> > +            $result->Close();
> >
> >             return $posts;
> >         }
> >
> > _______________________________________________
> > pLog-svn mailing list
> > pLog-svn at devel.lifetype.net
> > http://devel.lifetype.net/mailman/listinfo/plog-svn
> >
>
> **************************************
> Jon Daley
> http://jon.limedaley.com/
>
> All generalizations are bad.
> -- R. H. Grenier
> _______________________________________________
> pLog-svn mailing list
> pLog-svn at devel.lifetype.net
> http://devel.lifetype.net/mailman/listinfo/plog-svn
>


More information about the pLog-svn mailing list