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

oscar at devel.lifetype.net oscar at devel.lifetype.net
Tue Apr 11 21:39:08 GMT 2006


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;
         }



More information about the pLog-svn mailing list