[pLog-svn] Database layout

[Oscar Renalias]

That's a good point. But on the other hand, data is always loaded even
if it's not displayed...

On 5/22/06, Jon Daley <plogworld at jon.limedaley.com> wrote:
>         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.
> _______________________________________________
> pLog-svn mailing list
> pLog-svn at devel.lifetype.net
> http://devel.lifetype.net/mailman/listinfo/plog-svn
>