[pLog-svn] Database layout

Mon May 22 13:19:37 GMT 2006

I really don't know how to say it anymore :) The complex COUNT
function that was needed in 1.0.x to get the number of posts per
category is now gone because we have one field called num_posts and
num_active_posts in the lt_articles_cateegories tables that takes care
of this. As a de-normalized field, it does not need to be calculated
every time and it's only updated every time a new post is added or
updated.

The only way I see to improve the speed of the query used in
getBlogArticles is by removing the in_main_page feature. That's the
only reason why we need to join all the three tables (lt_articles,
lt_articles_categories and lt_article_categories_link), because when
generating the front page of the blog we need to check which
categories should not be displayed. That's all. If we remove the
feature we'll get rid of this problem but removing features is perhaps
not the most suitable approach :)

Oscar

On 5/22/06, Ayalon <ayalon at blog.nl> wrote:
> Depends, i'm not talking about making the queries small. Take out functions
> that are not often used, like the number of articles in a category. Parts
> that are not so much used and take a lot of mysql, to make the system more
> flexible..
>
> ----- Original Message -----
> From: "Jon Daley" <plogworld at jon.limedaley.com>
> To: <plog-svn at devel.lifetype.net>
> Sent: Monday, May 22, 2006 2:35 PM
> Subject: Re: [pLog-svn] Database layout
>
>
> > As for splitting queries, I don't know if that is useful, unless there is
> > a way to disable parts of the queries.  I think running one big query is
> > preferable to a bunch of little ones?  I have a guy on my server who
> > writes tons of little queries, because they are easier for him to
> > understand, he causes the most load on the mysql database.
> >
> > On Mon, 22 May 2006, Ayalon wrote:
> >
> >> 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
> >> _______________________________________________
> >> pLog-svn mailing list
> >> pLog-svn at devel.lifetype.net
> >> http://devel.lifetype.net/mailman/listinfo/plog-svn
> >>
> >
> > **************************************
> > Jon Daley
> > http://jon.limedaley.com/
> >
> > A mighty fortress is our God, a bulwark never failing.
> > -- Martin Luther
> > _______________________________________________
> > 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
>