[mythtv-users] anyone using percona instead of mysql (stock)?

[Michael T. Dean]

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.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

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.  :)

Mike