[mythtv] Scheduler needs table keys?

[Paul Andreassen]

On Tue, 30 Jan 2007 10:03 pm, Chris Pinkham wrote:
> * On Tue Jan 30, 2007 at 09:58:36AM +0000, Stuart Auchterlonie wrote:
> <snip> a bunch of "create index" statements
>
> > > No real difference on any of these that I could tell.  I applied them
> > > one by one and did a "mythbackend --resched" after each.
> >
> > Well that's not really going to be a good test, since after the first
> > reschedule all of the referenced DB tables will have been read into
> > memory, so having an index isn't going to speed things up much.
> >
> > Where it will help is when doing a reschedule after your machine has
> > been busy doing other things, so then just the indexes need reading
> > into memory.
>
> I have a dedicated database server, my tables and indexes are usually
> always in memory. :)
>
> Even if they're in memory, doing an index search is much faster than
> doing a complete table scan, so if it was using an index that gave any
> significant speedup, I should have seen a difference in runtime.  All
> of my runs were less than 1 second difference, which is about a 3%
> deviation plus or minus when you consider the average was around
> 16.5 seconds.

That is very disappointing.  The only others I would suggest are the removal 
of any related to <>, because the MySQL site comments mention that indexes 
aren't used for them.

CREATE INDEX tp ON oldrecorded (title, programid);
CREATE INDEX tf ON oldrecorded (title, findid);
CREATE INDEX ts ON oldrecorded (title, subtitle);
CREATE INDEX td ON oldrecorded (title, description(128));
CREATE INDEX csmg ON program (chanid, starttime, manualid, generic);

Was going suggest the next one but saw that 'recorded' has two indexes for 
this already.  Not sure why MySQL would allow identical indexes be created.
CREATE INDEX t ON recorded (title);

Anyway if these don't help, I give up.

Thanks for trying,
Paul
--