[mythtv-users] Slow MySQL query after delete

[f-myth-users at media.mit.edu]

    > Date: Wed, 5 Sep 2007 19:29:39 -0700 (PDT)
    > From: crs23 <pvr at groundhog.pair.com>

    > Yep, I'm still here.  I applied your patch and ran it.  Unfortunately, I'm
    > still getting about 30 seconds.  Thanks for trying.  With various
    > defragmentations and my.cnf adjustments and running optimize on the DB I
    > appear to be down from 36 seconds to 30 seconds, but that's really not good
    > enough.  As I mentioned in another post in this thread I think I'll just
    > have to remove rows from recordmatch.  I also pointed out that this SELECT
    > should be considered a serious bug in MythTV.

There's a whole lot of information on what the scheduler is doing and
why some queries are taking a long time from the -dev archives at
http://www.gossamer-threads.com/lists/engine?do=post_view_flat;post=249300;page=1;mh=-1;list=mythtv;sb=post_latest_reply;so=ASC
(The title is "Scheduler needs table keys?")

Reading it could inform a lot of discussion on this topic.

cpinkham, bjm, and paulx thrashed the issue around for quite a while
in that thread from Jan 27 to Jan 30; this was the thread I'd
originally said was from the early-March timeframe, because I
was misremembering it as being a part of cpinkham's patch to keep
video-dumping threads from getting locked (as much) during scheduler
updates; instead, the scheduler conversation preceded that patch by
a month but made it obvious -why- it was so important (30-60 sec
locks!) to keep such threads from blocking on the DB at all.  So if
you hunted around for that thread and didn't find it, blame my memory.

crs23:  You are -definitely- not the only one to be experiencing very
long reschedule times.  The referenced thread makes it apparent that
certain details can lead to long times; for example, it's that thread
where cpinkham (et al, I believe) mentions that changing from "All
Record" to "Channel Record" can make very dramatic differences in
runtime, and thus why I take MT Dean's frequent "any time any
channel" recommendations with a grain of salt---yes, it's conceptually
the easiest and guarantees you won't be surprised by something popping
up on a weird channel, but I worry that a DB full of these will have
spectacularly slower scheduler queries, and it's often possible to
group channels into equivalence classes where an episode might migrate
among a small set but won't pop up clear across the, uh, "dial". :)
Granted, doing that requires either duplicating "single channel"
rules, or punting the easy interface and writing such rules by hand.

[If there were a way to have the best of both worlds, of course, then
I'd go change my per-channel rules to all-channel rules, but I changed
'em back in part due to the above discussion and it helped.  A little.]

P.S.  That discussion is now 8 months old, but I presume that everyone
in it except me was running SVN, and I therefore presume that this
might still be an issue even in current SVN.