Ticket #11011 (closed Bug Report - General: Won't Fix)
Opened 9 months ago
Last modified 2 months ago
getAspect() SQL query is slow
|Reported by:||nordmark@…||Owned by:||kormoc|
|Component:||Plugin - MythWeb||Version:||Master Head|
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.
comment:1 Changed 2 months ago by kormoc
- Status changed from new to closed
- Resolution set to Won't Fix