getAspect() SQL query is slow

[nordmark@…]

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

[kormoc]

The problem is the last mark may not be the actual show's aspect ratio. Digital channels can be HD for the show and SD for the ads or even the opposite.

So given the a show like the following:

• Intro - 30 seconds HD
• Ads - 3 minutes SD
• Show 10 minutes HD
• Ads - 5 minutes SD
• Show - 8 minutes HD
• Ads - 6 minutes SD

VS

• Intro - 30 seconds SD
• Ads - 3 minutes HD
• Show 10 minutes SD
• Ads - 5 minutes HD
• Show - 8 minutes SD
• Ads - 6 minutes HD

it gets really complex to know which one is the show. So what we do is we add up all the durations for the segments and use the aspect ratio of the longest duration, which is why it's so complex.

I'm sorry, but it's just not easy to know without the work