[pLog-svn] r1208 - plog/trunk/class/dao

[oscar at devel.plogworld.net]

Author: oscar
Date: 2005-02-25 12:13:47 +0000 (Fri, 25 Feb 2005)
New Revision: 1208

Modified:
   plog/trunk/class/dao/articlecategories.class.php
   plog/trunk/class/dao/articles.class.php
Log:
added a couple of performance improvements. I got my hands on a 13.000
articles database and these changes turned my local installation from unusable
to usable... in fact, the getArticleCategories method was generating a query
that had to loop through millions of rows while in fact the boundaries of
the query could have been limited by adding the blog identifier.
Now the query executes in 0.20 sec... which is still quite a lot but
much better than now!
Please report if this has any side effect...
For those interested, I could also put this db dump online for testing purposes
(I will have to scramble the data in it to make it unreadable for privacy
purposes but otherwise I think it could help us)


Modified: plog/trunk/class/dao/articlecategories.class.php
===================================================================
--- plog/trunk/class/dao/articlecategories.class.php	2005-02-25 06:46:46 UTC (rev 1207)
+++ plog/trunk/class/dao/articlecategories.class.php	2005-02-25 12:13:47 UTC (rev 1208)
@@ -404,9 +404,14 @@
 		
 		
 		/**
-         * returns all the categories that an article has been assigned to
+         * returns all the categories that an article has been assigned to an article
+         *
+         * @param artcleId The artcle id
+         * @param blogId The blog id. This is an optiona parameter however, it is recommended to
+         * include the blog id parameter in order to speed up the SQL query!
+         * @return An aray of ArticleCategories object that were assigned to this article.         
          */
-        function getArticleCategories( $articleId )
+        function getArticleCategories( $articleId, $blogId = -1 )
         {
 			$prefix = $this->getPrefix();
 			$query = "SELECT c.id AS id, c.name AS name, c.url AS url, c.blog_id AS blog_id, 
@@ -415,7 +420,12 @@
 						  c.mangled_name AS mangled_name, IF(a.id IS NULL, 0, COUNT(*)) AS num_articles, a.date AS last_update 
 						  FROM {$prefix}articles_categories c LEFT JOIN {$prefix}article_categories_link l
 						  ON c.id=l.category_id LEFT JOIN {$prefix}articles a ON a.id = l.article_id 
-						  WHERE a.id = $articleId GROUP BY c.id";
+						  WHERE a.id = $articleId ";
+			if( $blogId > -1 ) {
+				$query .= " AND a.blog_id = '".Db::qstr($blogId)."' AND c.blog_id = '".Db::qstr($blogId)."'";	
+			}
+						  
+			$query .= " GROUP BY c.id";
             
             $result = $this->Execute( $query );
 
@@ -438,9 +448,11 @@
 		 * returns all the article categories given an array of category ids
 		 *
 		 * @param articleIds an array of category ids
+		 * @param blogId The blog id to which these article categories belong. It is recommeded to pass this
+		 * parameter as it will greatly speed up the query.
 		 * @return an array of ArticleCategory objects
 		 */
-        function getArticleCategoriesByIds( $articleIds )
+        function getArticleCategoriesByIds( $articleIds, $blogId = -1 )
         {
         	
 			$prefix = $this->getPrefix();
@@ -450,8 +462,13 @@
 						  c.mangled_name AS mangled_name, IF(a.id IS NULL, 0, COUNT(*)) AS num_articles, a.date AS last_update 
 						  FROM {$prefix}articles_categories c LEFT JOIN {$prefix}article_categories_link l
 						  ON c.id=l.category_id LEFT JOIN {$prefix}articles a ON a.id = l.article_id 
-						  WHERE a.id IN (".$articleIds.") GROUP BY c.id, a.id";
-            
+						  WHERE a.id IN (".$articleIds.") ";
+			if( $blogId > -1 ) {
+				$query .= " AND a.blog_id = '".Db::qstr( $blogId )."' AND c.blog_id = '".Db::qstr( $blogId )."'";	
+			}			
+						    
+			$query .= " GROUP BY c.id, a.id";
+			            
             $result = $this->Execute( $query );
 
             // it's impossible that an article has no categories, but

Modified: plog/trunk/class/dao/articles.class.php
===================================================================
--- plog/trunk/class/dao/articles.class.php	2005-02-25 06:46:46 UTC (rev 1207)
+++ plog/trunk/class/dao/articles.class.php	2005-02-25 12:13:47 UTC (rev 1208)
@@ -398,7 +398,7 @@
             $ids = substr($ids, 0, -1);          
             $articleComments = $this->comments->getPostCommentsByIds( $ids, COMMENT_ORDER_NEWEST_FIRST, COMMENT_STATUS_ALL );
 			$articleTrackbacks = $this->trackbacks->getArticleTrackbacksByIds( $ids );
-			$articleCategories = $this->categories->getArticleCategoriesByIds( $ids );
+			$articleCategories = $this->categories->getArticleCategoriesByIds( $ids, $blogid );
 			$articleTexts = $this->getArticlesText( $ids );
             $fields = $this->customfields->getArticleCustomFieldsValuesByIds( $ids );
             
@@ -1018,9 +1018,9 @@
         /**
          * returns all the categories that an article has been assigned to
          */
-        function getArticleCategories( $articleId )
+        function getArticleCategories( $articleId, $blogId = -1 )
         {
-			return $this->categories->getArticleCategories( $articleId );
+			return $this->categories->getArticleCategories( $articleId, $blogId );
         }
 
         /**
@@ -1089,7 +1089,7 @@
             // we can use this auxiliary function to help us...
             $date = Timestamp::getDateWithOffset( $query_result['date'], $timeDiff );
 
-			$articleCategories = $this->getArticleCategories( $query_result['id'] );
+			$articleCategories = $this->getArticleCategories( $query_result['id'], $query_result['blog_id'] );
 			//$this->log->debug($articleCategories);
             $categoryIds = Array();
 			foreach( $articleCategories as $category )