[pLog-svn] Database layout

[Ayalon]

Yes, I'm using caching. This gives some improvements. But I run cache at
3600, so every hour I run this queries and almost every hour it updates, so
cache because becomes then very much useless..

It has to make every time new temp tables etc.. If I can assist in some of
your actions, please let me know..

-----Oorspronkelijk bericht-----
Van: plog-svn-bounces at devel.lifetype.net
[mailto:plog-svn-bounces at devel.lifetype.net] Namens Oscar Renalias
Verzonden: zaterdag 20 mei 2006 16:33
Aan: plog-svn at devel.lifetype.net
Onderwerp: Re: [pLog-svn] Database layout

Articles::getNumberPostsPerMonth() and it's used to calculate which months
have posts in them.

> These queries came up by only accessing the main page of the blog, in 
> plain url's. Also it almost looks like 2 queries are doing almost the 
> same thing, is this a misconfig in the template on my site? Or 
> different functions, diferrent things to look for?

I will look into using the same query for the first two, it might even be
possible.

Regarding optimizations, I am finding it very difficult. I found these two
good resources:

http://dev.mysql.com/doc/refman/4.1/en/order-by-optimization.html
http://dev.mysql.com/doc/refman/4.1/en/group-by-optimization.html

Optimizing ORDER BY and GROUP BY to use indexes instead of filesorts and
temporary tables seemed quite difficult.

The only way I can think of to optimize the last query is by precalculating
the data (during INSERT and UPDATE operations to the lt_articles table) and
storing it separately in another table, so that loading the archives for any
blog is nothing more than one simple SELECT operation.

By the way, does anybody have MySQL's query cache enabled? I was playing
with it a couple of days ago and the results where quite good (most of the
queries were being cached and served a lot faster from the cache!)
_______________________________________________
pLog-svn mailing list
pLog-svn at devel.lifetype.net
http://devel.lifetype.net/mailman/listinfo/plog-svn