[pLog-svn] Database layout

[Jon Daley]

 	Seems like a lot of work for little gain.  Those people who are 
going to run huge sites and worry about extreme performance are going to 
have to make some changes anyway, it seems like they could just modify 
their templates to not include the counts per category, archive, etc.


On Sun, 21 May 2006, Oscar Renalias wrote:
>> ================================================
>> 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
>
> The problem is the GROUP BY and the ORDER BY clauses.
>
> The only I see to make this query faster is to move the archives data to its 
> own table, and get it updated after every post insertion or update. The table 
> should have one column for the year, one for the month and one for the number 
> of posts during that year and month.
>
> It's actually a bit trickier than that becuase we need to keep a counter for 
> only published posts and one for all posts. Additionally, the function that 
> calculates these counters needs to keep in mind whether future posts should 
> be shown, so we actually need 4 counters: one for published posts so far, one 
> for all posts so far, one for published posts including posts with future 
> dates, and one for all posts including posts with future dates.
>
> Then the query to load the archives would be as easy a SELECT * FROM 
> lt_archives ORDER BY YEAR DESC, MONTH DESC or something like that.
>
> And no, this method does not deal with time difference.
> _______________________________________________
> pLog-svn mailing list
> pLog-svn at devel.lifetype.net
> http://devel.lifetype.net/mailman/listinfo/plog-svn

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

The colder the X-ray table, the more of your body is required on it.