[pLog-svn] db optimization

Oscar Renalias oscar at renalias.net
Thu Apr 13 18:44:01 GMT 2006


The good thing is that we haven't had the chance yet to implement data
caching for this plugin. Once we get that in place, this data will
only be loaded once and fetched thereafter from the cache... This is
actually one of the best candiates for caching because the list
blocked hosts/ip addresses does not change often at all, so it can be
reused many times. I have myself not added new blocked hosts for
months now, so we could most likely reduce those 32000 queries into a
few hundreds only (we've got to keep in mind Cache_Lite's lifetime for
cached data)

Oscar

On 4/13/06, Christoph Feddersen <feddersen at herr-der-ringe-film.de> wrote:
> Ah ok,
> this query appeared > 32.000 times in the logs. Yes, using IN () is
> faster than using OR on the same field.
>
> So the host_block_rules is a standard lifetype table, but it's only used
> in combination with this plugin?
>
> I'll skip plugins as a start and concentrate on the main application.
>
> Oscar Renalias wrote:
> > It's from a plugin, the 'hostblock' plugin to be more precise.
> >
> > But I recently changed this query, since mysql wasn't using the
> > indexes properly. Now the query looks something like the one below and
> > according to mysql's EXPLAIN output, it seems to be making a better
> > usage of the indexes defined on these two columns.
> >
> > SELECT * FROM plog_host_blocking_rules WHERE blog_id IN (1,0)
> > AND block_type = 2;
> >
> > If interested, get the pluging via svn from
> > http://deve.lifetype.net/svn/plog/plugins/trunk/hostblock
> >
> > Oscar
> >
> > On 4/11/06, Christoph Feddersen <feddersen at herr-der-ringe-film.de> wrote:
> >> Can somebody tell me where I can find this query in the sources:
> >> SELECT * FROM plog_host_blocking_rules WHERE blog_id = 1 OR blog_id = 0
> >> AND block_type = 2;
> >>
> >> I'm too stupid to find it in the 1.1 codebase.
> >>
> >> Thanks,
> >> Christoph
> >> _______________________________________________
> >> 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
>


More information about the pLog-svn mailing list