[pLog-svn] Database layout

Jon Daley plogworld at jon.limedaley.com
Mon May 22 13:43:59 GMT 2006


 	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


More information about the pLog-svn mailing list