[mythtv] Joining on a calculated column

David Engel gigem at comcast.net
Tue Jan 11 15:45:53 EST 2005


On Tue, Jan 11, 2005 at 12:17:35PM -0500, mythtv at donoghue.org wrote:
> > Or you could create a temporary table in a separate query (as long as
> > you have permission to do so).  Works nicely for some things, but beware
> > of speed issues (mysql's optimizer might do this for your
> > repeated-calculation thing, anyway).
> >
> > -Chris
> 
> Yep, if its a complex formula, then a temp table would be the way to go. 
> Permissions shouldnt be an issue as long as you preface the temp table
> with a "#"

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
-- 
David Engel
gigem at comcast.net


More information about the mythtv-dev mailing list