[mythtv-users] Duplicate detection

Mike Perkins mikep at randomtraveller.org.uk
Sat Sep 24 09:56:14 UTC 2016


On 24/09/16 09:17, Jan Ceuleers wrote:
> On 20/09/16 18:20, Michael T. Dean wrote:
>> Totally not tested, but something along the lines of:
>>
>> LEFT OUTER JOIN oldrecorded ON (oldrecorded.title = program.title AND
>> oldrecorded.subtitle = program.subtitle AND oldrecorded.recstatus IN
>> (-3, 11))
>> ...
>> (your custom query stuff)
>> AND oldrecorded.recstatus IS NULL
>>
>> and anything you can do to further limit the join (conditions in the ON
>> clause) will improve performance.  Probably needs a lot of changes, but
>> it's a start.
>
> Mike,
>
> I'd like to test the query manually before I insert it as a recording
> rule (given the fairly exploderous effect I noticed while playing around
> with this power rule earlier, in terms of resource usage).
>
> Do you know precisely what query mythtv will execute for a power rule if
> record.description = XXX and
> record.whatevercolumnisusedforadditionaltables = YYY?
>
> Note that in my tests I was unable to get the LEFT OUTER JOIN to work if
> the FROM clause contains more than one table (it said that program.title
> was unknown in the ON clause shown above). So in my mysql cli tests I
> had to drop the restriction on channel.callsign by taking channel out of
> the FROM clause and of course dropping the corresponding conditions from
> the WHERE clause. The following query works:
>
> SELECT program.title FROM program
>   LEFT OUTER JOIN oldrecorded
>   ON (program.title = oldrecorded.title AND oldrecorded.subtitle =
> program.subtitle AND oldrecorded.recstatus IN (-3, 11))
>   WHERE program.category in
> ('film','movie','action','romance','crime','comedy','western','drama','tv
> movie','mystery','historical/period drama','literary adaptation', 'war',
> 'musical', 'crime drama') AND TIMESTAMPDIFF(MINUTE, program.starttime,
> program.endtime) >= 70 AND program.airdate >= 1970 AND
> oldrecorded.recstatus IS NULL;
>
> BTW: a nice trick I spotted on the wiki is to include the LEFT OUTER
> JOIN ... ON clauses in the additional tables field in order to ensure
> that it shows up before the WHERE keyword which must be part of the
> hard-coded query mythtv constructs.
>
A note that may help - if you're linking tables then it might be worth setting up temporary indexes 
on the fields used for joins (assuming they don't already have them). This should make a BIG 
difference to the amount of processing required.

-- 

Mike Perkins



More information about the mythtv-users mailing list