[pLog-svn] r3234 - plog/trunk/class/summary/dao
Jon Daley
plogworld at jon.limedaley.com
Mon May 1 23:29:33 GMT 2006
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
More information about the pLog-svn
mailing list