[mythtv] Joining on a calculated column

Bruce Markey bjm at lvcm.com
Tue Jan 11 19:06:40 EST 2005


David Engel wrote:

> You all tell me which is more efficient, temp table or duplicating the
> function.  The function is below.  It goes in the big, ugly, scheduler
> query and needs to returned in the SELECT list and used in JOINs for
> recorded and oldrecorded.
> 
>     QString progfindid = QString(
> "(CASE record.type "
> "  WHEN %1 "
> "   THEN to_days(date_sub(program.starttime, interval "
> "                date_format(record.findtime, '%H:%i') hour_minute)) "
> "  WHEN %2 "
> "   THEN floor((to_days(date_sub(program.starttime, interval "
> "               date_format(record.findtime, '%H:%i') hour_minute)) - "
> "               to_days(record.findtime))/7) * 7 + to_days(record.findtime) "
> "  ELSE 0 "
> " END) ")
>         .arg(kFindDailyRecord)
>         .arg(kFindWeeklyRecord);

David, while I don't mean to discourage any optimization =), as
ugly as this is, I don't think it has a real appreciable impact
on run time. Because if the CASE, only one or the other of these
will apply when there is a FindDaily or FindWeekly (letting the cat
further out of the bag ;-) and a NOOP otherwise. Depending on how
much these are used, it may only apply to a couple dozen items out
of a couple hundred in the reclist.

As cryptic as it looks, these are only a couple time functions and
integer math and may have no more impact than, say, the TIME_TO_SEC(),
DAYOFWEEK() and TO_DAYS() later on in TUQ when it is matching
times per record type.

In testing, it's hard to come up with a truly fair comparison but
I'm not seeing any big difference between using lots of Find* rules
with this code vs lots of traditional rules.

--  bjm


More information about the mythtv-dev mailing list