[pLog-svn] Database layout

[Mark Wu]

Hi Ayalon:

Thanks for information. Please kindly keep reviewing the sql querys ...

Hi Oscar and Jun:

================================================
For Articles::getBlogArticles and/or Articles::getRecentArticles:
================================================

1. Maybe we can add in_main_page field to lt_articles... When we update
lt_artical_category, we update this field too.....
2. And, we also can merge the lt_article_category_link and lt_article..

Then,  the sql can change to

SELECT * from lt_articles where blogId=3 and in_man_page=1 and date <=
'20060520150501' ORDER BY date DESC LIMIT 10;

Sorry, I already forgot why we separate these two tables ... So, maybe it is
only a wild thought.

================================================
For  Articles::getNumberPostsPerMonth() :
================================================

As you asid. Maybe ...
1.  Add year and month to field for lt_articles..
2. And we have to update these two field when article update..

Then, the sql can change to:

SELECT COUNT(id) AS 'count' where blog_id=3 and date<now() group by
year,month order by year,month desc

And we can add index to year_month ... That's might help ...

*** One thing I can not make sure is "not sure this method can deal with
time difference or not" ....  need more discussion here.

Mark

> >                                                 YEAR(date) 
> AS 'year',
> >                                                 MONTH(date) 
> AS 'month'
> >                                               FROM plog_articles
> >                                               WHERE status = 1 AND 
> > blog_id =
> > 3
> >                                               AND date <= NOW()
> >                                               GROUP BY
> > YEAR(date),MONTH(date)
> >                                               ORDER BY YEAR(date)
> > DESC,MONTH(date) DESC;


> -----Original Message-----
> From: plog-svn-bounces at devel.lifetype.net 
> [mailto:plog-svn-bounces at devel.lifetype.net] On Behalf Of 
> Oscar Renalias
> Sent: Saturday, May 20, 2006 10:33 PM
> To: plog-svn at devel.lifetype.net
> Subject: Re: [pLog-svn] Database layout
> 
> > 
> ----------------------------------------------------------------------
> > ------
> > SELECT a.id as id, a.id, a.date,
> >                               
> > a.user_id,a.blog_id,a.status,a.properties,
> >                              a.num_reads, a.slug, 1 AS relevance, 
> > a.num_comments AS num_comments,
> > 	
> > a.num_nonspam_comments AS num_nonspam_comments, a.num_trackbacks AS 
> > num_trackbacks,
> > 	
> > a.num_nonspam_trackbacks AS num_nonspam_trackbacks,
> > 	
> > a.global_category_id AS global_category_id,
> > 							 
> a.in_summary_page
> > AS in_summary_page
> > 							 FROM 
> plog_articles
> > a, plog_articles_categories c,
> >                              plog_article_categories_link l WHERE   
> > a.blog_id
> > = 3 AND c.id = l.category_id AND a.id = l.article_id AND 
> > c.in_main_page = 1
> > AND a.status = '1' AND a.date <= '20060520150501' GROUP BY a.id   
> > ORDER BY
> > a.date DESC LIMIT 10;
> 
> Articles::getBlogArticles and/or Articles::getRecentArticles.
> 
> > SELECT a.id as id, a.id, a.date,
> >                               
> > a.user_id,a.blog_id,a.status,a.properties,
> >                              a.num_reads, a.slug, 1 AS relevance, 
> > a.num_comments AS num_comments,
> > 	
> > a.num_nonspam_comments AS num_nonspam_comments, a.num_trackbacks AS 
> > num_trackbacks,
> > 	
> > a.num_nonspam_trackbacks AS num_nonspam_trackbacks,
> > 	
> > a.global_category_id AS global_category_id,
> > 							 
> a.in_summary_page
> > AS in_summary_page
> > 							 FROM 
> plog_articles
> > a, plog_articles_categories c,
> >                              plog_article_categories_link l WHERE   
> > a.blog_id
> > = 3 AND c.id = l.category_id AND a.id = l.article_id AND 
> > c.in_main_page = 1
> > AND a.status = '1' AND a.date <= '20060520150500' GROUP BY a.id   
> > ORDER BY
> > a.date DESC LIMIT 0, 15
> 
> Articles::getBlogArticles and/or Articles::getRecentArticles.
> 
> > SELECT COUNT(id) AS 'count',
> >                                                 YEAR(date) 
> AS 'year',
> >                                                 MONTH(date) 
> AS 'month'
> >                                               FROM plog_articles
> >                                               WHERE status = 1 AND 
> > blog_id =
> > 3
> >                                               AND date <= NOW()
> >                                               GROUP BY
> > YEAR(date),MONTH(date)
> >                                               ORDER BY YEAR(date)
> > DESC,MONTH(date) DESC;
> > 
> ----------------------------------------------------------------------
> > ----
> 
> Articles::getNumberPostsPerMonth() and it's used to calculate 
> which months have posts in them.
> 
> > These queries came up by only accessing the main page of 
> the blog, in 
> > plain url's. Also it almost looks like 2 queries are doing 
> almost the 
> > same thing, is this a misconfig in the template on my site? Or 
> > different functions, diferrent things to look for?
> 
> I will look into using the same query for the first two, it 
> might even be possible.
> 
> Regarding optimizations, I am finding it very difficult. I 
> found these two good resources:
> 
> http://dev.mysql.com/doc/refman/4.1/en/order-by-optimization.html
> http://dev.mysql.com/doc/refman/4.1/en/group-by-optimization.html
> 
> Optimizing ORDER BY and GROUP BY to use indexes instead of 
> filesorts and temporary tables seemed quite difficult.
> 
> The only way I can think of to optimize the last query is by 
> precalculating the data (during INSERT and UPDATE operations 
> to the lt_articles table) and storing it separately in 
> another table, so that loading the archives for any blog is 
> nothing more than one simple SELECT operation.
> 
> By the way, does anybody have MySQL's query cache enabled? I 
> was playing with it a couple of days ago and the results 
> where quite good (most of the queries were being cached and 
> served a lot faster from the cache!) 
> _______________________________________________
> pLog-svn mailing list
> pLog-svn at devel.lifetype.net
> http://devel.lifetype.net/mailman/listinfo/plog-svn