[mythtv] patch: mythweb listings

Andy Davidoff dert at pobox.com
Wed Mar 5 20:35:56 EST 2003


Erm, looks like I failed to remove some unrelated hackery.  This version
is more subtle.  Serves me right for trying to maintain multiple trees.
Apologies...


#if Andy Davidoff /* Mar 05, 18:44 */
> This patch dramatically improves the speed of the listings.php by issuing
> one query per timeslot versus 5 queries * number-of-channels * timeslots.
> 
> My listings page used to take about 8 seconds to generate and now takes
> about 2.5 seconds.  This could be faster if we altered the database, but...
#endif /* dert at pobox.com */
-------------- next part --------------
Index: mythweb/classes.php
===================================================================
RCS file: /var/lib/cvs/mythweb/classes.php,v
retrieving revision 1.6
diff -d -u -w -r1.6 classes.php
--- mythweb/classes.php	11 Dec 2002 20:57:44 -0000	1.6
+++ mythweb/classes.php	6 Mar 2003 01:34:10 -0000
@@ -137,7 +137,28 @@
 	var $duration;
 	var $recordColour;
 	var $typeColour;
+	var $whenRecord;
 
+	function willRecord()
+	{
+		return $this->whenRecord;
+	}
+	function alwaysRecord()
+	{
+		return $this->whenRecord & 1<<4;
+	}
+	function channelRecord()
+	{
+		return $this->whenRecord & 1<<3;
+	}
+	function singleRecord()
+	{
+		return $this->whenRecord & 1<<2;
+	}
+	function timeslotRecord()
+	{
+		return $this->whenRecord & 1<<1;
+	}
 	function setColours($a, $b)
 	{
 		$this->recordColour = $a;
Index: mythweb/functions.php
===================================================================
RCS file: /var/lib/cvs/mythweb/functions.php,v
retrieving revision 1.8
diff -d -u -w -r1.8 functions.php
--- mythweb/functions.php	2 Mar 2003 14:10:44 -0000	1.8
+++ mythweb/functions.php	6 Mar 2003 01:34:13 -0000
@@ -357,24 +357,42 @@
 //
 function setupPrograms ($channelarray, $timearray, $numbSlots)
 {
-	$channelindex = 0;
-	while ($mychannel = $channelarray[$channelindex])
-	{
 		for ($i = 0; $i < $numbSlots; $i += 1)
 		{
+		$channelindex = 0;
 			$mytime = $timearray[$i];
 			$sqltime = $mytime->sqltime;
-
-			$chanid = $mychannel->chanid;
-			
-			$query = "SELECT channel.chanid, starttime, endtime, title, subtitle, description, category, ((UNIX_TIMESTAMP(endtime) - UNIX_TIMESTAMP(starttime)) / 60 ) as duration, channel.channum FROM program,channel WHERE program.chanid = " . $chanid . " AND program.chanid = channel.chanid AND " .
-			"starttime < " . $sqltime . " AND endtime > " . $sqltime .
-			" ORDER BY channel.channum + 0, starttime;";
+		$query = "SELECT ".
+					"sum(record.type = 4)>0 as 'type4', ".
+					"sum(record.type = 3 and record.chanid = program.chanid)>0 as 'type3', ".
+					"sum(record.type = 2 and record.chanid = program.chanid and ".
+					"     record.starttime = sec_to_time(time_to_sec(program.starttime)) and ".
+					"     record.endtime = sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', " .
+					"sum(record.type = 1 and record.chanid = program.chanid and ".
+					"     record.starttime = sec_to_time(time_to_sec(program.starttime)) and ".
+					"     record.startdate = from_days(to_days(program.starttime)))>0 as 'type1', ".
+					"channel.chanid, ".
+					"channel.channum, ".
+					"program.starttime, ".
+					"program.endtime, ".
+					"program.title, ".
+					"program.subtitle, ".
+					"program.description, ".
+					"program.category, ".
+					"((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) / 60 ) as duration ".
+				"FROM channel left join program using (chanid) left join record using (title) ".
+				"WHERE program.starttime < " . $sqltime . " AND program.endtime > " . $sqltime." ".
+				"GROUP BY program.chanid ORDER BY channel.channum + 0;";
 
 			$result = mysql_query($query) or die("Gadzooks! I can't open the program table.");
 
  			while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
 			{
+			while ($line["channum"] != $channelarray[$channelindex]->channum)
+			{
+				$programarray[$channelindex][$i] = null;
+				$channelindex++;
+			}
 				$myprog = new ProgramInfo;
 				$myprog->title = $line["title"];
 				$myprog->subtitle = $line["subtitle"];
@@ -385,13 +403,16 @@
 				$myprog->progType = $line["category"];
 				$myprog->duration = $line["duration"];
 				$myprog->description = $line["description"];
+			$myprog->whenRecord += $line["type1"]<<1;
+			$myprog->whenRecord += $line["type2"]<<2;
+			$myprog->whenRecord += $line["type3"]<<3;
+			$myprog->whenRecord += $line["type4"]<<4;
 
 				$programarray[$channelindex][$i] = $myprog;
+			$channelindex++;
 			}
 
 			mysql_free_result($result);
-		}
-  		$channelindex += 1;
 	}
 	return $programarray;
 }
Index: mythweb/listings.php
===================================================================
RCS file: /var/lib/cvs/mythweb/listings.php,v
retrieving revision 1.11
diff -d -u -w -r1.11 listings.php
--- mythweb/listings.php	2 Mar 2003 14:10:44 -0000	1.11
+++ mythweb/listings.php	6 Mar 2003 01:34:13 -0000
@@ -210,34 +210,7 @@
 			}
 			$proginfo->setColours($finalColour, $finalColour);
 			$recordArrayIndex = 0;
-			//
-			//	Check and see if this is in Record
-			//	Once data
-			//
-			$isBeingSingleRecorded = isInOnceRecord($proginfo->chanid, $proginfo->startts);
-			if ($isBeingSingleRecorded)
-			{
-			    $proginfo->setColours($list_reccolour, $finalColour);
-			}
-			//while($aRecord = $recordArray[$recordArrayIndex])
-			//{
-			//	if($aRecord->chanid == $proginfo->chanid &&
-			//		$aRecord->starttime == $proginfo->startts)
-			//	{
-			//		$proginfo->setColours($list_reccolour, $finalColour);
-			//	}
-			//		
-			//	$recordArrayIndex += 1;	
-			//}
-			//
-			//	Check and see if this is an 
-			//	always record or a timeslot
-			//	record
-			//
-			if(isInAlwaysRecord($proginfo->title) ||
-			isInChannelAlwaysRecord($mychannel->chanid, $proginfo->title) ||
-			isInTimeslotRecord($proginfo->chanid, $proginfo->startts, $proginfo->endts, $proginfo->title))
-			{
+			if($proginfo->willRecord()) {
 				$proginfo->setColours($list_reccolour, $finalColour);
 			}
 


More information about the mythtv-dev mailing list