[pLog-svn] r2167 - plog/branches/plog-1.0.2/class/summary/dao
Jason King
jason at pixellation.com
Sun Jun 5 17:08:57 GMT 2005
Has anyone done anything with ADODB's Performance Tools?
adodb-perf.inc.php
Or anything similar to tune the queries?
oscar at devel.plogworld.net wrote:
>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;
> }
>
>_______________________________________________
>pLog-svn mailing list
>pLog-svn at devel.plogworld.net
>http://devel.plogworld.net/mailman/listinfo/plog-svn
>
>
More information about the pLog-svn
mailing list