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

[mythtv at phipps-hutton.freeserve.co.uk]

Quoting Dan Wilga <mythtv-dev2 at dwilga-linux1.amherst.edu>:

> 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
>

Gosh you crazy SQL guys. I've 7134615 entries in recordedseek and this  
/bin/sh fragment checks in about 7 seconds:

echo 'select distinct chanid, starttime from recordedseek;' \
| mythql \
| sort \
| while read c s
do
   count=$(echo "select title from recorded where chanid = '$c' and  
starttime = '$s';" | mythql | wc -l)
   if [ "$count" != 1 ]
   then
     echo $c $s
   fi
done


mythql is a shell script to call mysql on the mythconverg database and  
print out without heading.

Cheers,
Tim.



> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-dev
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org