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

[Nick Morrott]

On 21 April 2015 at 14:26, Dan Wilga
<mythtv-dev2 at dwilga-linux1.amherst.edu> 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?

Cheers,
Nick