Opened 14 years ago
Last modified 14 years ago
#7604 closed enhancement
Very slow query when delete recording — at Version 1
Reported by: | anonymous | Owned by: | Isaac Richards |
---|---|---|---|
Priority: | minor | Milestone: | unknown |
Component: | MythTV - General | Version: | 0.22 |
Severity: | low | Keywords: | delete recordings slow query |
Cc: | Ticket locked: | no |
Description (last modified by )
When I delete a recording through the frontend it takes over 15 seconds until the record is deleted. There is one very slow query that takes over 13 seconds:
SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime, program.endtime, program.title, program.subtitle, program.description, channel.channum, channel.callsign, channel.name, oldrecduplicate, program.category, sched_temp_record.recpriority, sched_temp_record.dupin, recduplicate, findduplicate, sched_temp_record.type, sched_temp_record.recordid, program.starttime - INTERVAL sched_temp_record.startoffset MINUTE AS recstartts, program.endtime + INTERVAL sched_temp_record.endoffset MINUTE AS recendts, program.previouslyshown, sched_temp_record.recgroup, sched_temp_record.dupmethod, channel.commmethod, capturecard.cardid, cardinput.cardinputid, UPPER( cardinput.shareable ) = 'Y' AS shareable, program.seriesid, program.programid, program.category_type, program.airdate, program.stars, program.originalairdate, sched_temp_record.inactive, sched_temp_record.parentid, ( CASE sched_temp_record.type WHEN 6 THEN sched_temp_record.findid WHEN 9 THEN to_days( date_sub( program.starttime, INTERVAL time_format( sched_temp_record.findtime, '%H:%i' ) HOUR_MINUTE ) ) WHEN 10 THEN floor( ( to_days( date_sub( program.starttime, INTERVAL time_format( sched_temp_record.findtime, '%H:%i' ) HOUR_MINUTE ) ) - sched_temp_record.findday ) /7 ) *7 + sched_temp_record.findday WHEN 7 THEN sched_temp_record.findid ELSE 0 END ), sched_temp_record.playgroup, oldrecstatus.recstatus, oldrecstatus.reactivate, program.videoprop +0, program.subtitletypes +0, program.audioprop +0, sched_temp_record.storagegroup, capturecard.hostname, recordmatch.oldrecstatus, sched_temp_record.avg_delay, channel.recpriority + cardinput.recpriority + ( cardinput.cardinputid = sched_temp_record.prefinput ) *2 AS powerpriority FROM recordmatch INNER JOIN sched_temp_record ON ( recordmatch.recordid = sched_temp_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 ) ORDER BY sched_temp_record.recordid DESC
Here is the explain of this query:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE capturecard ALL PRIMARY NULL NULL NULL 3 Using temporary; Using filesort 1 SIMPLE cardinput ALL NULL NULL NULL NULL 4 Using where; Using join buffer 1 SIMPLE recordmatch ALL recordid NULL NULL NULL 456 Using join buffer 1 SIMPLE sched_temp_record eq_ref PRIMARY PRIMARY 4 myth-test.recordmatch.recordid 1 1 SIMPLE channel eq_ref PRIMARY,sourceid PRIMARY 4 myth-test.recordmatch.chanid 1 Using where 1 SIMPLE program eq_ref PRIMARY,id_start_end,program_manualid,starttime PRIMARY 16 myth-test.recordmatch.chanid,myth-test.recordmatch.starttime,myth-test.recordmatch.manualid 1 1 SIMPLE oldrecstatus ALL NULL NULL NULL NULL 4224
Change History (1)
Note: See
TracTickets for help on using
tickets.
That's part of the scheduler, which is not tied to the UI updates.
Are you using an ext3 file system (or other file system with very slow delete times) without enabling the "Delete files slowly" setting in mythtv-setup? If so, please let us know and ask for help configuring Myth on the mythtv-users mailing list.