[mythtv] innodb vs myism preformance

[Daniel Manjarres]

So this is the structure of the 3 tables in my db. I guess the first
thing is to make sure they are not on crack. The next thing is to
figure out if I should be adding any indices or what to speed things
up. I apparently have 6 times the recordings that y'all have, so maybe
thats where I need to look. But I am not a SQL hacker, so I am not
sure how to proceed.


>mysqldump -d mythconverg recorded
-- MySQL dump 9.11
--
-- Host: localhost    Database: mythconverg
-- ------------------------------------------------------
-- Server version       4.0.23_Debian-4-log

--
-- Table structure for table `recorded`
--

CREATE TABLE `recorded` (
  `chanid` int(10) unsigned NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `endtime` datetime NOT NULL default '0000-00-00 00:00:00',
  `title` varchar(128) NOT NULL default '',
  `subtitle` varchar(128) NOT NULL default '',
  `description` text NOT NULL,
  `bookmark` varchar(128) default NULL,
  `editing` int(10) unsigned NOT NULL default '0',
  `cutlist` text,
  `hostname` varchar(255) NOT NULL default '',
  `category` varchar(64) NOT NULL default '',
  `autoexpire` int(11) NOT NULL default '0',
  `commflagged` int(10) unsigned NOT NULL default '0',
  `recgroup` varchar(32) NOT NULL default 'Default',
  `recordid` int(11) default NULL,
  `seriesid` varchar(12) NOT NULL default '',
  `programid` varchar(20) NOT NULL default '',
  `lastmodified` timestamp(14) NOT NULL,
  `filesize` bigint(20) NOT NULL default '0',
  `stars` float NOT NULL default '0',
  `previouslyshown` tinyint(1) default '0',
  `originalairdate` date default NULL,
  `preserve` tinyint(1) NOT NULL default '0',
  `findid` int(11) NOT NULL default '0',
  `deletepending` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`chanid`,`starttime`),
  KEY `endtime` (`endtime`),
  KEY `seriesid` (`seriesid`),
  KEY `programid` (`programid`)
) TYPE=InnoDB;

mysqldump -d mythconverg oldrecorded
-- MySQL dump 9.11
--
-- Host: localhost    Database: mythconverg
-- ------------------------------------------------------
-- Server version       4.0.23_Debian-4-log

--
-- Table structure for table `oldrecorded`
--

CREATE TABLE `oldrecorded` (
  `chanid` int(10) unsigned NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `endtime` datetime NOT NULL default '0000-00-00 00:00:00',
  `title` varchar(128) NOT NULL default '',
  `subtitle` varchar(128) NOT NULL default '',
  `description` text NOT NULL,
  `category` varchar(64) NOT NULL default '',
  `seriesid` varchar(12) NOT NULL default '',
  `programid` varchar(20) NOT NULL default '',
  `findid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`chanid`,`starttime`),
  KEY `endtime` (`endtime`),
  KEY `title` (`title`),
  KEY `seriesid` (`seriesid`),
  KEY `programid` (`programid`)
) TYPE=InnoDB;

mysqldump -d mythconverg program    
-- MySQL dump 9.11
--
-- Host: localhost    Database: mythconverg
-- ------------------------------------------------------
-- Server version       4.0.23_Debian-4-log

--
-- Table structure for table `program`
--

CREATE TABLE `program` (
  `chanid` int(10) unsigned NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `endtime` datetime NOT NULL default '0000-00-00 00:00:00',
  `title` varchar(128) NOT NULL default '',
  `subtitle` varchar(128) NOT NULL default '',
  `description` text NOT NULL,
  `category` varchar(64) NOT NULL default '',
  `airdate` year(4) NOT NULL default '0000',
  `stars` float NOT NULL default '0',
  `previouslyshown` tinyint(4) NOT NULL default '0',
  `category_type` varchar(64) NOT NULL default '',
  `title_pronounce` varchar(128) NOT NULL default '',
  `stereo` tinyint(1) NOT NULL default '0',
  `subtitled` tinyint(1) NOT NULL default '0',
  `hdtv` tinyint(1) NOT NULL default '0',
  `closecaptioned` tinyint(1) NOT NULL default '0',
  `partnumber` int(11) NOT NULL default '0',
  `parttotal` int(11) NOT NULL default '0',
  `seriesid` varchar(12) NOT NULL default '',
  `originalairdate` date default NULL,
  `showtype` varchar(30) NOT NULL default '',
  `colorcode` varchar(20) NOT NULL default '',
  `syndicatedepisodenumber` varchar(20) NOT NULL default '',
  `programid` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`chanid`,`starttime`),
  KEY `endtime` (`endtime`),
  KEY `title_pronounce` (`title_pronounce`),
  KEY `seriesid` (`seriesid`),
  KEY `programid` (`programid`),
  KEY `id_start_end` (`chanid`,`starttime`,`endtime`),
  KEY `title` (`title`)
) TYPE=InnoDB;