[mythtv] Why are there so many instances of recordedseek?

[Michael T. Dean]

On 04/21/2015 09:53 AM, Nick Morrott wrote:
> On 21 April 2015 at 14:26, Dan Wilga wrote:
>> On 4/20/15 9:04 PM, Nick Morrott wrote:
>>> You can find the start times of recordedseek entries that do not
>>> relate to current recordings using some brute force:
>>>
>>> SELECT DISTINCT `starttime`
>>> FROM `recordedseek`
>>> WHERE `starttime` NOT IN (SELECT `starttime` FROM `recorded`)
>>> ORDER By `starttime` ASC;
>> A subselect is a not a very efficient way to do this query, because of the
>> potential size. I tried it and stopped it after five minutes. It also
>> doesn't take into account the possibility of two programs having recorded at
>> the same time. This is still slow, but better:
>>
>> SELECT s.starttime, s.chanid FROM recordedseek s
>>    LEFT JOIN recorded r ON r.chanid = s.chanid AND r.starttime = s.starttime
>>    WHERE r.chanid IS NULL GROUP BY s.starttime, s.chanid
> I never promised it would be pretty and did use the term "brute force" :)
>
> On the test system from 2006 I was using (130 recordings, 900K entries
> in recordedseek) it took 30s for the original query to execute. Your
> new query? 1.2s! Thank you.
>
> Thanks for taking the time to post a much more efficient way - wonder
> if this could/should be added to the housekeeping code in the backend
> if it's not already there?

The recordedseek (and all(?--or at least all important) recorded* 
tables) are automatically pruned for orphan entries, already.  I think 
that's what you meant, right?

MythTV does database clean up.  No matter how much people seem to think 
it doesn't, it does.  :)

Mike