[pLog-svn] Database layout

[Jon Daley]

 	Can you look through the code to see where those queries are being 
called, and what their purpose is, and if there is a better way to write 
them?


  On Sat, 20 May 2006, Ayalon wrote:

> Hi All,
>
> New version needs immediate temp tables in mysql for the following queries,
> maybe it's a mysql bug, but anyway there's in my 2 configs no possibility
> not to have them written to disk (also with a clear cache on the server a
> clean /tmp and a mysql temp table size of 512mb)
>
> ----------------------------------------------------------------------------
> 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;
>
>
> 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
>
> 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;
> --------------------------------------------------------------------------
>
> 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?
>
> Regards
>
> _______________________________________________
> pLog-svn mailing list
> pLog-svn at devel.lifetype.net
> http://devel.lifetype.net/mailman/listinfo/plog-svn
>

**************************************
Jon Daley
http://jon.limedaley.com/

Even if you're on the right track,
you'll get run over if you just sit there.
-- Will Rogers