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

[George Nassas]

On Apr 21, 2015, at 9:26 AM, Dan Wilga <mythtv-dev2 at dwilga-linux1.amherst.edu> wrote:
> 
> 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:

In case anybody happens on this thread in the archives… when you’re testing for the (non) existence of matching rows, particularly involving indexes, it’s best to use EXISTS or NOT EXISTS:

mysql mythconverg -e 'select (select count(*) from recordedseek) recordedseek, (select count(*) from recorded) recorded'
+--------------+----------+
| recordedseek | recorded |
+--------------+----------+
|     18232366 |     3769 |
+--------------+----------+

I gave up on the first query after it pegged mysql for 15 minutes with no end in sight.

time mysql mythconverg -e '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'

real	0m15.464s
user	0m0.000s
sys	0m0.004s

time mysql mythconverg -e 'select distinct s.starttime, s.chanid from recordedseek s where not exists (select r.chanid from recorded r where r.chanid = s.chanid and r.starttime = s.starttime)'

real	0m0.026s
user	0m0.008s
sys	0m0.000s

In current master recording rows are related on recordedid which cleans up the queries nicely.

- George

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-dev/attachments/20150421/3cc751c9/attachment.html>