[mythtv] contrib: myth database optimization

Doug Goldstein cardoe at gmail.com
Tue Nov 30 21:38:36 UTC 2004

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

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.


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

More information about the mythtv-dev mailing list