<p>
Deleting programs has been slow for me I think since I started using MythTV, but it's been getting worse and now it's pretty bad. I think I've tracked it down to a particularly slow SQL query and I'm hoping some one will have some advice for making it fast(er).
</p><p>
First, the backend seems to hold off on this query for a few seconds. I can usually manage to delete two programs before it kicks in. But when it does the frontend blocks waiting for the backend which itself blocks waiting for mysql.
</p><p>
The query takes around 35 to 40 seconds. It's a pretty complicated query. I've included it below but here I'll just mention it JOINs ten tables. Here are the EXPLAIN results. Sorry for the wide columns.
</p>
<pre>
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
| 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 | 1984 | |
| 1 | SIMPLE | record | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.recordmatch.recordid | 1 | |
| 1 | SIMPLE | channel | eq_ref | PRIMARY | PRIMARY | 4 | mythconverg.recordmatch.chanid | 1 | Using where |
| 1 | SIMPLE | program | eq_ref | PRIMARY,id_start_end | PRIMARY | 16 | mythconverg.recordmatch.chanid,mythconverg.recordmatch.starttime,mythconverg.recordmatch.manualid | 1 | |
| 1 | SIMPLE | oldrecstatus | eq_ref | PRIMARY,title | PRIMARY | 160 | mythconverg.channel.callsign,mythconverg.recordmatch.starttime,mythconverg.program.title | 1 | |
| 1 | SIMPLE | oldrecorded | ref | title,programid | title | 130 | mythconverg.program.title | 36 | |
| 1 | SIMPLE | recorded | ref | programid,title | title | 130 | mythconverg.program.title | 5 | |
| 1 | SIMPLE | oldfind | eq_ref | PRIMARY | PRIMARY | 8 | mythconverg.recordmatch.recordid,func | 1 | Using index |
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
10 rows in set (0.02 sec)
</pre>
<p>Running this on my DB retrieves 3968 rows in set (39.54 sec). The record table has 87 entries and the recordmatch has 1984 (as you can see).</p>
<p>
The two ALL types are troubling as are the using temporary and using filesort but I'm not very familiar with databases or SQL so I don't know if I can improve this and if so how. Ideally I'm just missing some index and can adjust a table. If I need to remove info about previously recorded programs I can do that, too, though that's less desirable.</p>
<p>
Does anybody else have this problem? Or is my DB messed up somehow? That wouldn't surprise me as I've had not infrequent problems where MythTV decides to record programs that I haven't requested or forgets to record those I have. I suspect it's swapped one for the other but perhaps that's something to do with the downloaded program guide data.</p>
<p>Cheers,<br>-chris</p>
<p>
The query is:
</p>
<pre>
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.recpriority, record.prefinput, program.hdtv, program.closecaptioned, program.first, program.last, program.stereo 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;
</pre>
<br><hr align="left" width="300">
View this message in context: <a href="http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12446850">Slow MySQL query after delete</a><br>
Sent from the <a href="http://www.nabble.com/mythtv-users-f15550.html">mythtv-users mailing list archive</a> at Nabble.com.<br>