[pLog-svn] r2167 - plog/branches/plog-1.0.2/class/summary/dao
oscar at devel.plogworld.net
oscar at devel.plogworld.net
Sun Jun 5 12:18:19 GMT 2005
Author: oscar
Date: 2005-06-05 12:18:18 +0000 (Sun, 05 Jun 2005)
New Revision: 2167
Modified:
plog/branches/plog-1.0.2/class/summary/dao/summarystats.class.php
Log:
A few changes to improve the atrocious performance of the summary page... I was asked by bloggi.se if I could something about the performance of the summary page, since their server was pretty much dying every day because of this so I optimized the queries a bit:
- Removed the LIKE clauses to remove the 'congratulations...' posts, since the performance when this kind of clauses are included was pathetic. End-users are not going to like it but their servers will hopefully be thankful for it. The only way to recover this feature in 1.1 will be to add a field to the plog_articles table that determines whether a post should be in the front page or not.
- 2 new indexes should be added to the plog_articles table, one on the 'date' field and another one on the 'status'. Coupled with the next change, means for mysql a difference between scanning all rows in the plog_articles table and scanning *all* of them. For new users, this will be done in the wizard and for old users, we can document this in the release notes.
- Now all the statistics ('most read', 'most commented', etc) are based on the stuff posted in the last 7 days. It will make the listings a bit more dynamic and it will also make their sites work a bit better...
At least bloggi.se hasn't died in the last few days yet, thanks to this patch :)
Modified: plog/branches/plog-1.0.2/class/summary/dao/summarystats.class.php
===================================================================
--- plog/branches/plog-1.0.2/class/summary/dao/summarystats.class.php 2005-06-05 11:44:09 UTC (rev 2166)
+++ plog/branches/plog-1.0.2/class/summary/dao/summarystats.class.php 2005-06-05 12:18:18 UTC (rev 2167)
@@ -1,10 +1,9 @@
<?php
include_once( PLOG_CLASS_PATH."class/dao/model.class.php" );
- include_once( PLOG_CLASS_PATH."class/dao/articles.class.php" );
include_once( PLOG_CLASS_PATH."class/dao/blogs.class.php" );
- include_once( PLOG_CLASS_PATH."class/dao/users.class.php" );
- include_once( PLOG_CLASS_PATH."class/dao/articlecommentstatus.class.php" );
+ include_once( PLOG_CLASS_PATH."class/dao/articlecommentstatus.class.php" );
+ include_once( PLOG_CLASS_PATH."class/dao/articlestatus.class.php" );
/**
* maximum number of items that will be shown per page in the summary
@@ -23,11 +22,25 @@
*/
class SummaryStats extends Model
{
+
+ var $_now;
+ var $_sevenDaysAgo;
function SummaryStats()
{
// initialize ADOdb
$this->Model();
+
+ // common object for all methods so that we can reuse caches
+ $this->articles = new Articles();
+ $this->blogs = new Blogs();
+
+ // calculate the date limits
+ $t = new Timestamp();
+ $this->_now = $t->getTimestamp();
+ // 7 days ago
+ $t->subtractSeconds( 7 * 24 * 60 * 60 );
+ $this->_sevenDaysAgo = $t->getTimestamp();
}
/**
@@ -40,44 +53,38 @@
*/
function getMostCommentedArticles( $maxPosts = 0, $ignoreTopic = "", $ignoreText = "" )
{
+ include_once( PLOG_CLASS_PATH."class/dao/articles.class.php" );
+
$prefix = $this->getPrefix();
$query = " SELECT COUNT(*) as total_comments, a.*
FROM {$prefix}articles_comments AS c,
{$prefix}articles AS a,
- {$prefix}articles_text t,
{$prefix}blogs b
- WHERE c.article_id = a.id
- AND t.article_id = a.id
+ WHERE c.article_id = a.id
AND a.status = ".POST_STATUS_PUBLISHED."
AND c.status = ".COMMENT_STATUS_NONSPAM."
AND b.id = a.blog_id
AND b.status = ".BLOG_STATUS_ACTIVE."
- AND a.date <= NOW()";
+ AND a.date <= ".$this->_now;
- // ignore certain topics and/or certain texts
- /*if( $ignoreTopic != "" )
- $query .= " AND t.topic NOT LIKE '%".Db::qstr( $ignoreTopic )."%' ";
-
- if( $ignoreText != "" )
- $query .= " AND t.text NOT LIKE '%".Db::qstr( $ignoreTopic )."%' ";*/
-
$query .= " GROUP BY c.article_id ORDER BY total_comments DESC ";
if( $maxPosts > 0 )
$query .= " LIMIT 0,".$maxPosts;
- $result = $this->_db->Execute( $query );
+ $result = $this->Execute( $query );
if( !$result ){
return Array();
}
$posts = Array();
- $articles = new Articles();
while( $row = $result->FetchRow()) {
- array_push( $posts, $articles->_fillArticleInformation($row));
+ array_push( $posts, $this->articles->_fillArticleInformation($row));
}
+
+ $result->Close();
return $posts;
}
@@ -93,47 +100,39 @@
*/
function getMostReadArticles( $maxPosts = 0, $ignoreTopic = "", $ignoreText = "" )
{
- $prefix = $this->getPrefix();
- $query = " SELECT
- a.id as id,
- a.properties as properties,
- a.date as date,
- a.user_id as user_id,
- a.blog_id as blog_id,
- a.status as status,
- a.num_reads as num_reads,
- a.slug as slug,
- t.article_id as article_id,
- t.text as text
- FROM {$prefix}articles a, {$prefix}articles_text t, {$prefix}blogs b
- WHERE a.id = t.article_id
- AND a.status = ".POST_STATUS_PUBLISHED."
- AND TO_DAYS(NOW()) - TO_DAYS(date) < 7
- AND a.blog_id = b.id AND b.status = ".BLOG_STATUS_ACTIVE;
- // ignore certain topics and/or certain texts
- if( $ignoreTopic != "" )
- $query .= " AND t.topic NOT LIKE '".Db::qstr( $ignoreTopic )."' ";
- /*if( $ignoreText != "" )
- $query .= " AND t.text NOT LIKE '".Db::qstr( $ignoreText )."' ";*/
+ $prefix = $this->getPrefix();
+ $query = " SELECT
+ a.id as id,
+ a.properties as properties,
+ a.date as date,
+ a.user_id as user_id,
+ a.blog_id as blog_id,
+ a.status as status,
+ a.num_reads as num_reads,
+ a.slug as slug
+ FROM {$prefix}articles a
+ WHERE status = ".POST_STATUS_PUBLISHED."
+ AND a.date <= ".$this->_now." AND a.date > ".$this->_sevenDaysAgo;
$query .= " ORDER BY a.num_reads DESC ";
if( $maxPosts > 0 )
$query .= " LIMIT 0,".$maxPosts;
- $result = $this->_db->Execute( $query );
+ $result = $this->Execute( $query );
if( !$result )
return Array();
$posts = Array();
- $articles = new Articles();
while( $row = $result->FetchRow()) {
- $post = $articles->_fillArticleInformation($row);
+ $post = $this->articles->_fillArticleInformation($row);
array_push( $posts, $post );
}
+
+ $result->Close();
return $posts;
}
@@ -153,18 +152,19 @@
if( $maxBlogs > 0 )
$query .= " LIMIT 0,".$maxBlogs;
- $result = $this->_db->Execute( $query );
+ $result = $this->Execute( $query );
if( !$result ){
return Array();
}
$blogs = Array();
- $blogdao = new Blogs();
while( $row = $result->FetchRow()) {
- $blog = $blogdao->_fillBlogInformation( $row );
+ $blog = $this->blogs->_fillBlogInformation( $row );
$blogs[$blog->getId()] = $blog;
}
+
+ $result->Close();
return $blogs;
}
@@ -183,23 +183,25 @@
FROM {$prefix}articles AS a
INNER JOIN {$prefix}blogs AS b
ON b.id=a.blog_id AND b.status=".BLOG_STATUS_ACTIVE.
- " GROUP BY a.blog_id ORDER BY rank DESC ";
+ " WHERE a.date >= ".$this->_sevenDaysAgo." AND a.date <= ".$this->_now."
+ GROUP BY a.blog_id ORDER BY rank DESC ";
if( $maxBlogs > 0 )
$query .= " LIMIT 0,".$maxBlogs;
- $result = $this->_db->Execute( $query );
+ $result = $this->Execute( $query );
if( !$result ){
return Array();
}
$blogs = Array();
- $blogdao = new Blogs();
while( $row = $result->FetchRow()) {
- $blog = $blogdao->_fillBlogInformation( $row );
+ $blog = $this->blogs->_fillBlogInformation( $row );
$blogs[$blog->getId()] = $blog;
}
+
+ $result->Close();
return $blogs;
}
@@ -212,6 +214,7 @@
*/
function getAllUsersPaged( $page, $itemsPerPage )
{
+ include_once( PLOG_CLASS_PATH."class/dao/users.class.php" );
// calculate the limits...
if( $page > 0 ) {
$start = (($page - 1) * $itemsPerPage);
@@ -237,6 +240,8 @@
$user = $usersDao->_fillUserInformation( $row, true );
$users[$user->getId()] = $user;
}
+
+ $result->Close();
return $users;
}
@@ -253,54 +258,44 @@
*/
function getRecentArticles( $maxPosts, $ignoreTopic = "", $ignoreText = "" )
{
+ include_once( PLOG_CLASS_PATH . "class/data/timestamp.class.php" );
+ include_once( PLOG_CLASS_PATH."class/dao/articles.class.php" );
+
+ $t = new Timestamp();
+ $date = $t->getTimestamp();
$prefix = $this->getPrefix();
- $query = "SELECT a.id as id, a.id,t.topic,t.text,a.date,
+ $query = "SELECT a.id as id, a.id,a.date,
a.user_id,a.blog_id, a.status, a.properties,
a.num_reads, a.slug
FROM {$prefix}articles a,
- {$prefix}articles_categories c,
- {$prefix}article_categories_link l,
- {$prefix}articles_text t,
{$prefix}blogs b
- WHERE t.article_id = a.id
- AND TO_DAYS(NOW()) - TO_DAYS(a.date) < 7
- AND l.article_id = a.id
- AND l.category_id = c.id
- AND c.in_main_page = 1
+ WHERE a.date >= ".$this->_sevenDaysAgo." AND a.date <= ".$this->_now."
AND a.blog_id = b.id
AND b.status = ".BLOG_STATUS_ACTIVE."
- AND a.status = ".POST_STATUS_PUBLISHED."
- AND a.date < NOW()";
+ AND a.status = ".POST_STATUS_PUBLISHED;
+ $query .= " ORDER BY a.date DESC LIMIT 0, $maxPosts";
- // in case we'd like to ignore certain posts based on a topic (like the registration message!)
- if( $ignoreTopic != "" ) {
- $query .= " AND t.topic NOT LIKE '".Db::qstr( $ignoreTopic )."' ";
- }
- // in case we'd like to ignore certain posts based on their contents (like the registration message!)
- /*if( $ignoreText != "" ) {
- $query .= " AND t.text NOT LIKE '".Db::qstr( $ignoreText )."' ";
- }*/
- $query .= " GROUP BY a.id ORDER BY a.date DESC LIMIT 0, $maxPosts";
+ $result = $this->Execute( $query );
- $result = $this->_db->Execute( $query );
-
if( !$result )
return Array();
$blogs = Array();
$posts = Array();
$i = 0;
- $articles = new Articles();
+
while( ($row = $result->FetchRow()) && ($i < $maxPosts) ) {
if (!in_array($row["blog_id"], $blogs))
{
$blogs[] = $row["blog_id"];
- array_push( $posts, $articles->_fillArticleInformation($row) );
+ array_push( $posts, $this->articles->_fillArticleInformation($row) );
$i++;
}
}
+
+ $result->Close();
return $posts;
}
@@ -322,7 +317,7 @@
if( $maxPosts > 0 )
$query .= " LIMIT 0,". ($maxPosts * 3);
- $result = $this->_db->Execute( $query );
+ $result = $this->Execute( $query );
if( !$result )
return false;
@@ -336,16 +331,18 @@
if (empty($maxPosts))
{
- array_push( $posts, $articles->_fillArticleInformation($row));
+ array_push( $posts, $this->articles->_fillArticleInformation($row));
$count++;
}
else if($count <= $maxPosts && empty($ids[$row["blog_id"]]))
{
$ids[$row["blog_id"]] = true;
- array_push( $posts, $articles->_fillArticleInformation($row));
+ array_push( $posts, $this->articles->_fillArticleInformation($row));
$count++;
}
}
+
+ $result->Close();
return $posts;
}
More information about the pLog-svn
mailing list