[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