[mythtv] contrib: myth database optimization

[Doug Goldstein]

I actually hacked into mythfilldatabase a call to OPTIMIZE TABLE on
the tables that get jossled around on a nightly basis (based on
looking at which tables could be optimized in phpMyAdmin). I think
that'd probably be the best solution. It helped the first time I did
it (some operations got noticably faster). But doing it every night
keeps the tables fairly clean so I can't say I notice the difference
now.

Just to give you some comparison. After 3 weeks my database had
achieved 2.3MB overhead out of a 53.1MB database. That's roughly 4%
but it was contained mainly within 4 tables.

I'd say the next step in optimizing the SQL would be capture all the
statements that the system makes and run them through EXPLAIN and see
if we can tweak them.

-Doug

On Tue, 30 Nov 2004 17:02:49 +0000, Ed Wildgoose <lists at wildgooses.com> wrote:
> Asher Schaffer wrote:
> 
> >I haven't looked into this much, but I think some good automated DB
> >optimizing would be a really nice addition.  Personally, my DB
> >knownledge is all with Postgres not mysql, does mysql have anything
> >similar to clustering in Postgres, where a table gets reordering
> >according to an index?
> >
> >
> 
> I don't think that mysql has clustered indexes, but I suspect it would
> be of minimal benefit in most cases.  The DB is not that large in most
> of the tables (except the program table).
> 
> Check out "mysqloptimize" and perhaps stick it in your cron?
> 
> For optimisation, the biggest difference I saw was from turning on the
> query cache.  Check the docs for details, I seem to remember that you
> just need to add a line like:
> query_cache_size = 4000000
> in your my.cnf file?
> 
> Ed W
> 
> 
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
>