[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

On 09/06/2007 03:04 PM, crs23 wrote:
> Michael T. Dean wrote:
>   
>> Though ugly, the BUSQ (Big, Ugly Scheduler Query) seems to work (and
>> scale) for everyone else using Myth, so unless you have a particularly
>> large-scale setup (i.e. thousands of channels and hundreds/thousands of
>> recording rules each generating multiple matches), it should work for
>> you, too.  Can you provide specifics of your configuration (# of
>> channels and #--and approximate type--of recording rules) so we can
>> determine if the problem is actually a Myth issue before trying to fix
>> the BUSQ (which, generally, no one ever wants to touch).
> 'BUSQ', I like that.
>
> I have 81 channels and I have 87 rows in the record table.

That's definitely not large-scale for Myth.  However, I can't say what
kind of performance you should expect on your hardware (which seems a
little on the underpowered side for a master backend--especially if it's
a combined frontend/backend machine).

>   Is the latter
> the number of recording rules?

Yep.

>   That's *way* more than I want.  Checking the
> schedules page on MythWeb shows several rules, often identical, for many
> programs.  This sounds like a problem.
>   

I don't think much testing has been done on systems having identical
recording rules.  Though it shouldn't cause issues, cleaning out the
duplicates (triplicates?  whatever) would significantly simplify the
query's processing, so doing so would be very much to your advantage.

> Assuming that it is a problem I suspect how it may have happened.  MythTV
> has not infrequently failed to record requested programs and recorded
> unrequested programs for reasons unknown to me.  When that's happened I've
> remove the magical new recording rules and added back the missing ones. 
> Perhaps the missing ones were still there but somehow disabled?  I'll go
> through the list and remove unwanted entries.
>   

Good plan.

> That said and again admiting to not being all that familiar with databases,
> it's my understanding that a query that scales linearly with the size of the
> database is not considered a good query.  When the DB has to scan every row
> doesn't that suggest the need for an index?

Perhaps.  I'll leave this for database experts and also mention that
IIRC, indices have changed pretty dramatically in SVN trunk--possibly
due to other DB schema changes in trunk--so expending effort at trying
to optimize that query should probably be done using SVN trunk. 
Otherwise, the effort will have only an ephemeral effect.

Mike