[mythtv] Why are there so many instances of recordedseek?
Michael T. Dean
mtdean at thirdcontact.com
Tue Apr 21 14:05:45 UTC 2015
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. :)
More information about the mythtv-dev