Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#2014 closed defect (invalid)

excessive sql query for listing previously recorded programs

Reported by: oa@… Owned by: Isaac Richards
Priority: minor Milestone: unknown
Component: mythtv Version: 0.19
Severity: medium Keywords:
Cc: Ticket locked: no

Description

My backend/frontend combo machine needs 15 seconds to respond on every switch to "watch recordings" screen or mythweb's recorded programs page. The reason for this is the query to MySQL, below:

SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime, program.endtime, program.title, program.subtitle, program.description, channel.channum, channel.callsign, channel.name, oldrecorded.endtime IS NOT NULL AS oldrecduplicate, program.category, record.recpriority, record.dupin, recorded.endtime IS NOT NULL AS recduplicate, oldfind.findid IS NOT NULL AS findduplicate, record.type, record.recordid, program.starttime - INTERVAL record.startoffset minute AS recstartts, program.endtime + INTERVAL record.endoffset minute AS recendts, program.previouslyshown, record.recgroup, record.dupmethod, channel.commfree, capturecard.cardid, cardinput.cardinputid, UPPER(cardinput.shareable) = 'Y' AS shareable, program.seriesid, program.programid, program.category_type, program.airdate, program.stars, program.originalairdate, record.inactive, record.parentid, (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) , record.playgroup, oldrecstatus.recstatus, oldrecstatus.reactivate, channel.recpriority + cardinput.preference FROM recordmatch INNER JOIN record ON (recordmatch.recordid = record.recordid) INNER JOIN program ON (recordmatch.chanid = program.chanid AND recordmatch.starttime = program.starttime AND recordmatch.manualid = program.manualid) INNER JOIN channel ON (channel.chanid = program.chanid) INNER JOIN cardinput ON (channel.sourceid = cardinput.sourceid) INNER JOIN capturecard ON (capturecard.cardid = cardinput.cardid) LEFT JOIN oldrecorded as oldrecstatus ON ( oldrecstatus.station = channel.callsign AND oldrecstatus.starttime = program.starttime AND oldrecstatus.title = program.title ) LEFT JOIN oldrecorded ON ( record.dupmethod > 1 AND oldrecorded.duplicate <> 0 AND program.title = oldrecorded.title AND ( (program.programid <> AND program.generic = 0 AND program.programid = oldrecorded.programid) OR (oldrecorded.findid <> 0 AND oldrecorded.findid = (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = OR oldrecorded.programid = ) AND (((record.dupmethod & 0x02) = 0) OR (program.subtitle <> AND program.subtitle = oldrecorded.subtitle)) AND (((record.dupmethod & 0x04) = 0) OR (program.description <> AND program.description = oldrecorded.description)) ) ) ) LEFT JOIN recorded ON ( record.dupmethod > 1 AND recorded.duplicate <> 0 AND program.title = recorded.title AND recorded.recgroup <> 'LiveTV' AND ( (program.programid <> AND program.generic = 0 AND program.programid = recorded.programid) OR (recorded.findid <> 0 AND recorded.findid = (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) ) OR ( program.generic = 0 AND (program.programid = OR recorded.programid = ) AND (((record.dupmethod & 0x02) = 0) OR (program.subtitle <> AND program.subtitle = recorded.subtitle)) AND (((record.dupmethod & 0x04) = 0) OR (program.description <> AND program.description = recorded.description)) ) ) ) LEFT JOIN oldfind ON (oldfind.recordid = recordmatch.recordid AND oldfind.findid = (CASE record.type WHEN 6 THEN record.findid WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday WHEN 7 THEN record.findid ELSE 0 END) ) ORDER BY record.recordid DESC

It seems to me that a) that's pretty complex, b) it probably doesn't really need to fetch everything, and c) there's likely a join bug in there somewhere, because it returns "9972 rows in set (14.19 sec)", while the explain statement tells that the largest component table in that query is less than half that size:

{{{+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+ | 1 | SIMPLE | cardinput | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort | | 1 | SIMPLE | capturecard | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.cardinput.cardid | 1 | Using index | | 1 | SIMPLE | recordmatch | ALL | recordid | NULL | NULL | NULL | 5334 | | | 1 | SIMPLE | program | eq_ref | PRIMARY,id_start_end | PRIMARY | 16 | mythconverg.recordmatch.chanid,mythconverg.recordmatch.starttime,mythconverg.recordmatch.manualid | 1 | | | 1 | SIMPLE | channel | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.program.chanid | 1 | Using where | | 1 | SIMPLE | oldrecstatus | eq_ref | PRIMARY,title | PRIMARY | 156 | mythconverg.channel.callsign,mythconverg.program.starttime,mythconverg.program.title | 1 | | | 1 | SIMPLE | record | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.recordmatch.recordid | 1 | | | 1 | SIMPLE | oldrecorded | ref | title,programid | title | 128 | mythconverg.program.title | 6 | | | 1 | SIMPLE | recorded | ref | programid,title | title | 128 | mythconverg.program.title | 1 | | | 1 | SIMPLE | oldfind | eq_ref | PRIMARY | PRIMARY | 8 | mythconverg.recordmatch.recordid,func | 1 | Using index | +----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+}}}

My table is fully optimized (by a nightly backup + analyze job), so that query plan is pretty much the best job mysql 4.1.20 can do for the statement and schema.

I tried to make sense of this query, but I can not even begin to figure out what it's supposed to do. Why does a query that presumably exists to list recorded programs try to eliminate duplicates from the schedule, for example?

Change History (2)

comment:1 Changed 13 years ago by Isaac Richards

Resolution: invalid
Status: newclosed

That query certainly isn't being run for that screen.

comment:2 Changed 13 years ago by oa@…

Huh. Well, it's repeatedly being run, entering watch recordings takes 15 seconds, and that's what I see in 'show full processlist'. What can I say?

Note: See TracTickets for help on using tickets.