[mythtv-users] Slow MySQL query after delete

[Tom Dexter]

On 9/6/07, David Brodbeck <gull at gull.us> wrote:
>
> On Sep 6, 2007, at 2:36 PM, f-myth-users at media.mit.edu wrote:
> > Oh, whoops.  Hadn't considered that.  (Does Myth have a "suggested" or
> > (gulp) "supported" set of storage engines, or is it claimed that any
> > storage engine MySQL supports will "work"?  I'm assuming 99% of
> > everybody leaves theirs at the default, but of course we don't know.)
>
> I'm actually curious if there are any performance benefits to using
> one over the other.  My understanding is InnoDB's main benefit over
> MyISAM is transaction support, which MythTV doesn't use, so I'm
> guessing no.  There may be other factors I'm not thinking of, though.
>

I've worked a good bit with both MyISAM and InnoDB tables.  I'd be
very surprised if InnoDB tables didn't work in mythtv, as I can't
think of any sql syntax that works in MyISAM but not InnoDB.  Some
utility programs however are only for MyISAM...I can't remember which
offhand.

There are of course features in InnoDB that won't work with MyISAM
(such as transactions), however you can still have them in code...they
just don't do anything on MyISAM tables.

It's hard to say how they compare as far as performance goes.  I've
read many opinions that MyISAM is often faster since it doesn't have
to deal with the overhead of transaction rollbacks etc.

The biggest thing that would throw off a lot of people about using
InnoDB tables is that the actual table and index data is no longer
under the database directories as they are with MyISAM (the *.MYD and
*.MYI files).  Only the tablename.frm files exist there, and the
actual data and indexes all reside in the InnoDB data file(s)...their
location and initial size and how they auto extend etc are all
configurable.  Also, the data in those files does NOT separate data
from different databases...they're all in the same file(s).  The mysql
database must always use MyISAM tables by the way.

For anyone who wants to get adventurous, it's not hard to convert with
alter table:

ALTER TABLE table_name ENGINE InnoDB;

Tom