[mythtv-users] Solved: Duplicate detection
jan.ceuleers at gmail.com
Sun Oct 2 08:44:05 UTC 2016
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.
To close this topic: I found a solution.
The solution I went for isn't actually a power search: I could not make
that to work due to the length restriction of the Additional Tables
field (column subtitle in table record is a varchar(128)) and I didn't
want to change the schema due to the possibility (likelihood?) of fallout.
As a matter of fact, I was able to fit the LEFT OUTER JOIN ... ON
clauses that Mike suggested into the additional tables field by using an
alias as follows:
LEFT OUTER JOIN oldrecorded o ON (o.title=program.title AND
o.subtitle=program.subtitle AND o.recstatus IN (-3,11))
This is indeed shorter than 128 characters.
But testing it clarified that it did something I didn't want, namely it
never re-records movies, full stop. I don't mind occasionally watching a
So I decided to keep my existing power search unchanged, and instead to
change the category of movies that were already recorded during the past
X months to something that won't match the power search, by
incorporating the following in my daily script after new guide data has
been inserted and before a final mythutil --resched:
update program set program.category='recorded'
#select title from program
drama','literary adaptation', 'war','musical', 'crime drama')
AND TIMESTAMPDIFF(MINUTE, program.starttime,program.endtime) >= 70
AND program.airdate >= 1970
AND NOT EXISTS
and timestampdiff(month, oldrecorded.starttime,
This executes very quickly and without the need for a join.
Thanks to all who helped.
More information about the mythtv-users