[pLog-svn] Database layout

[Ayalon]

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