[mythtv-users] anyone using percona instead of mysql (stock)?
Michael T. Dean
mtdean at thirdcontact.com
Tue Dec 20 20:52:59 UTC 2011
On 12/20/2011 09:46 AM, Jay Ashworth wrote:
> From: "Gary Buhrmaster"
>> I too have been running innodb for years at this point, and
>> my recollection was that the 'select count(*)' on innodb
>> resulted in a table scan (and an index lookup on myisam?),
>> and that mythtv used that construct in numerous queries.
> Well, SELECT COUNT(1) generally works fine, and doesn't touch the data at all,
> in my experience.
The issue to which Gary was referring is the fact that MyISAM data
format stores the exact row count in the binary data file; whereas
InnoDB stores no such value. Therefore, a COUNT(*) (or COUNT(1)) on an
InnoDB table requires reading the table (and appropriate locking,
etc.--due to the differences in multi-transaction usage between MyISAM
and InnoDB); whereas on a MyISAM table it's basically just returned
without any data access. Note, also, that COUNT(column) has a different
effect from COUNT(*)--it returns the count of rows with non-NULL
values--which means it is most likely not a valid replacement.
And, once again, I'd just like to reiterate the fact that we only
support databases using MyISAM storage for all tables, with the
exception of MythWeather tables (which are InnoDB). Unless you are an
aspiring database admin--or you already are a database admin, and feel
that you don't get to do enough of the things you do at work when you're
at home--your best bet is to use a supported (unmodified) database
schema and trust that we'll convert the storage engine when
appropriate. And, even if you want to do database administration at
home, unless you want to spend a lot of time reading MythTV code and
trying to diagnose and work around issues that arise when modifying the
schema for which MythTV code is designed, your best bet is to use the
unmodified schema. And, unless you know of MySQL performance problems
with MythTV, there's no reason to worry about improving MySQL
performance with MythTV. :)
More information about the mythtv-users