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 <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] > http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev >
_______________________________________________ mythtv-dev mailing list [EMAIL PROTECTED] http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev
