[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