[mythtv-users] Duplicate detection

Jan Ceuleers jan.ceuleers at gmail.com
Sat Oct 1 11:18:42 UTC 2016


On 01/10/16 12:51, 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.
> 
> Been playing around with this. Implementing it is hampered by a length
> restriction on the subtitle column in the record table:
> 
> mysql> show columns from record where Field="subtitle";
> +----------+--------------+------+-----+---------+-------+
> | Field    | Type         | Null | Key | Default | Extra |
> +----------+--------------+------+-----+---------+-------+
> | subtitle | varchar(128) | NO   |     |         |       |
> +----------+--------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
> 
> I'll mull it over some more. Perhaps I can move the subtitle check to
> the WHERE clause, or even drop it altogether.

Sorry, that was too cryptic.

The problem is that the "additional tables" field used in a custom
(power search) recording rule is stored in the subtitle field of the
record table. That is where the LEFT OUTER JOIN ... ON stuff has to go
in order to ensure that it appears in the query before the WHERE clause.

So the varchar(128) is too short to contain the whole of the LEFT OUTER
JOIN ... ON that Mike suggested.

Options I'm considering:

- Dropping the subtitle check in the ON clause, because movies generally
don't have subtitles anyway. But this would also match non-movies with
movies.
- Finding a way to move the subtitle check into the WHERE clause. More
complicated, and also worse performance-wise.
- Perhaps I can use an alias for the oldrecorded table; this should save
a few characters here and there.

HTH, Jan


More information about the mythtv-users mailing list