[pLog-svn] Database layout

Mark Wu markplace at gmail.com
Mon May 22 14:38:50 GMT 2006


Hi Jon & Ayalon:

The num_articles already part of lt_article_categories , so .... mysql don't
need to caculate it any more when we use the getBlogArticles or
gerRecentArticles .

** The number_articles  will update when article insert, update and delete.

That's why Oscar said even we don't display it, the data still loaded.

The problem of these two queries is "order by" and "group by", unless we
de-normalize those tables again or separate those part to another table like
lt_archived_articles,  or mysql will use the tmp file sort every time when
these two queries fire ... :(

Mark 

> -----Original Message-----
> From: plog-svn-bounces at devel.lifetype.net 
> [mailto:plog-svn-bounces at devel.lifetype.net] On Behalf Of Jon Daley
> Sent: Monday, May 22, 2006 9:44 PM
> To: plog-svn at devel.lifetype.net
> Subject: Re: [pLog-svn] Database layout
> 
>  	Once you take them out, what do you do with them?  
> Maybe have an object that the templates can call with 
> "not-often-used" functions?
> 
> Oscar, when you said data is loaded anyway, I am not sure 
> what you mean.
> In my panel.template, I use articleCategory->getNumArticles() 
> The query to calculate the number of articles wouldn't be 
> called if I removed that, right?
> 
> On Mon, 22 May 2006, Ayalon 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
> >
> 
> **************************************
> Jon Daley
> http://jon.limedaley.com/
> 
> Even if you're on the right track,
> you'll get run over if you just sit there.
> -- Will Rogers
> _______________________________________________
> pLog-svn mailing list
> pLog-svn at devel.lifetype.net
> http://devel.lifetype.net/mailman/listinfo/plog-svn



More information about the pLog-svn mailing list