[pLog-svn] Database layout

[Oscar Renalias]

> ================================================
> 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.