id	summary	reporter	owner	description	type	status	priority	milestone	component	version	severity	resolution	keywords	cc	mlocked
11011	getAspect() SQL query is slow	nordmark@…	Rob Smith	"I noticed that the Recorded programs web page started to take tens of seconds to load. During this time, the MySQL database server is continuously running at full CPU utilization, sorting queries for recordedmarkup.type. These SQL queries are found in the getAspect() function of the Program class. In the recordedmarkup table there are a number of recordings where there thousands of custom aspect ratio (type 14) marks.

Looking at the query (found in modules/tv/classes/Program.php):
$sh = $db->query('SELECT recordedmarkup.type,
recordedmarkup.data
FROM recordedmarkup
WHERE recordedmarkup.chanid = ?
AND recordedmarkup.starttime = FROM_UNIXTIME(?)
AND recordedmarkup.type IN (10, 11, 12, 13, 14)
GROUP BY recordedmarkup.type
ORDER BY SUM((SELECT IFNULL(rm.mark, recordedmarkup.mark)
FROM recordedmarkup AS rm
WHERE rm.chanid = recordedmarkup.chanid
AND rm.starttime = recordedmarkup.starttime
AND rm.type IN (10, 11, 12, 13, 14)
AND rm.mark > recordedmarkup.mark
ORDER BY rm.mark ASC LIMIT 1)- recordedmarkup.mark) DESC
LIMIT 1',
$this->chanid,
$this->recstartts
);
it looks overly complicated, and even with my limited understanding of SQL, it looks at least quadratic in the number of aspect ratio marks, since each one is compared to every other. Indeed, one such query can take several seconds to execute.

Since it comes down to picking one (probably just the first or last) aspect ratio mark, there surely must be a quicker way to write the query without a sub-query for each mark, like 'ORDER BY recordedmarkup.mark ASC LIMIT 1' or something similar.

Arne"	Bug Report - General	closed	minor	unknown	Plugin - MythWeb	Master Head	medium	Fixed			0