[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