[pLog-svn] r2167 - plog/branches/plog-1.0.2/class/summary/dao
Oscar Renalias
phunkphorce at gmail.com
Sun Jun 5 20:49:16 GMT 2005
I used MySQL's EXPLAIN query, which will show you how the query will
be executed, which keys/indexes will be used, what kind of
optimizations it will use and how many rows it will scan. It really
helped this time... (and ADOdb's performance tools wouldn't have
helped this time, as they do not record this information)
Oscar
On 6/5/05, Jason King <jason at pixellation.com> wrote:
> 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
> >
> >
>
> _______________________________________________
> 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