[pLog-svn] Database layout

[Ayalon]

Yes indeed, the problem is not the load etc on even huge sites. If a site 
gets a lot of hits etc it can be so heavy if there are a lot of posts, 
categories etc because of data loading which is not used anyway.

Maybe it's better to do it like vbbulletin, in the admin you can enable or 
disable some things for your user as a kind of server config. This way 
people with performance problems, or problems with there host can 
immediately act.

Anyway the categorie query needs to be adjusted not to scan the tables for 
counts etc... Big queries should be divided in parts i think, this makes the 
system more flexible and better to handle for all people in all enviroments 
(and this is what we want no?!)

----- Original Message ----- 
From: "Oscar Renalias" <oscar at renalias.net>
To: <plog-svn at devel.lifetype.net>
Sent: Monday, May 22, 2006 7:11 AM
Subject: Re: [pLog-svn] Database layout


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
>
_______________________________________________
pLog-svn mailing list
pLog-svn at devel.lifetype.net
http://devel.lifetype.net/mailman/listinfo/plog-svn