[mythtv-users] Power search record exclude shows

[Ronald Frazier]

On Thu, Aug 25, 2011 at 1:06 PM, Jan Ceuleers <jan.ceuleers at gmail.com> wrote:
> If the list of shows to be excluded is getting a bit too long (for
> example a power search record rule matching movies on multiple channels
> and you don't want to record movies you already have on DVD), then you
> can add something like
>
> AND NOT lower(program.title) IN (SELECT lower(title) FROM theDVDsIvegot)
>
> Then you just create a table called theDVDsIvegot within mythconverg
> with a title column, and add all of your DVD titles to it.
>
> Might slow the scheduler down though, so I don't actually do it this
> way.

The subselect might be a cleaner looking (in terms of writing the
query) way to do it, but unless things have changed in mysql
optimization in the last few years, the general recommendation is that
doing a subselect like that performs much worse than doing an outer
join. You'd be better off to do a left join against the theDVDsIvegot
table, and then in the where clause you'd do  theDVDsIvegot.title IS
NULL.

On the other hand, when you are just using hardcoded constants, doing
the IN clause is fine, because the optimizer will actually build a
binary tree of the values and can do a comparison against it quite
quickly.

-- 
Ron Frazier