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 sphery)

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)

comment:1 Changed 14 years ago by sphery

Description: modified (diff)
Status: newinfoneeded_new

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.

Note: See TracTickets for help on using tickets.