[pLog-svn] Database layout

[Jon Daley]

 	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