[mythtv-users] Solved: Duplicate detection

[Jan Ceuleers]

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
movie again.

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
where
  program.category in
('film','movie','action','romance','crime','comedy','western','drama','tvmovie','mystery','historical/period
drama','literary adaptation', 'war','musical', 'crime drama')
    AND TIMESTAMPDIFF(MINUTE, program.starttime,program.endtime) >= 70
    AND program.airdate >= 1970
    AND NOT EXISTS
      (
        select oldrecorded.title,oldrecorded.subtitle
        from oldrecorded
        where program.title=oldrecorded.title
          and program.subtitle=oldrecorded.subtitle
          and timestampdiff(month, oldrecorded.starttime,
program.starttime)<=12
          and oldrecorded.recstatus=-3
      );

This executes very quickly and without the need for a join.

Thanks to all who helped.

Cheers, Jan