[mythtv-users] Slow MySQL query after delete
crs23
pvr at groundhog.pair.com
Sun Sep 2 07:02:37 UTC 2007
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).
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.
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.
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
| 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)
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).
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.
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.
Cheers,
-chris
The query is:
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;
--
View this message in context: http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12446850
Sent from the mythtv-users mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20070902/1c57e514/attachment.htm
More information about the mythtv-users
mailing list