[pLog-svn] Database layout

Oscar Renalias oscar at renalias.net
Sat May 20 14:32:59 GMT 2006


> ---------------------------------------------------------------------- 
> ------
> 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!)


More information about the pLog-svn mailing list