<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">On Apr 21, 2015, at 9:26 AM, Dan Wilga <<a href="mailto:mythtv-dev2@dwilga-linux1.amherst.edu" class="">mythtv-dev2@dwilga-linux1.amherst.edu</a>> wrote:<br class=""><div><blockquote type="cite" class=""><br class="Apple-interchange-newline"><div class=""><span style="font-family: Helvetica; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">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:</span><br style="font-family: Helvetica; font-size: 14px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""></div></blockquote></div><br class=""><div class="">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:</div><div class=""><br class=""></div><div class=""><div class=""><div class="">mysql mythconverg -e 'select (select count(*) from recordedseek) recordedseek, (select count(*) from recorded) recorded'</div><div class="">+--------------+----------+</div><div class="">| recordedseek | recorded |</div><div class="">+--------------+----------+</div><div class="">| 18232366 | 3769 |</div><div class="">+--------------+----------+</div></div><div class=""><br class=""></div></div><div class="">I gave up on the first query after it pegged mysql for 15 minutes with no end in sight.</div><div class=""><br class=""></div><div class=""><div class="">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'</div><div class=""><br class=""></div><div class="">real<span class="Apple-tab-span" style="white-space:pre"> </span>0m15.464s</div><div class="">user<span class="Apple-tab-span" style="white-space:pre"> </span>0m0.000s</div><div class="">sys<span class="Apple-tab-span" style="white-space:pre"> </span>0m0.004s</div></div><div class=""><br class=""></div><div class=""><div class="">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)'</div><div class=""><br class=""></div><div class="">real<span class="Apple-tab-span" style="white-space:pre"> </span>0m0.026s</div><div class="">user<span class="Apple-tab-span" style="white-space:pre"> </span>0m0.008s</div><div class="">sys<span class="Apple-tab-span" style="white-space:pre"> </span>0m0.000s</div></div><div class=""><br class=""></div><div class="">In current master recording rows are related on recordedid which cleans up the queries nicely.</div><div class=""><br class=""></div><div class="">- George</div><div class=""><br class=""></div></body></html>