[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