<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16525" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi, </FONT></DIV>
<DIV><FONT face=Arial size=2>Not sure if this helps, but this week I sent in a
query about the size of the database I had, 168MB. Someone (Michael Dean I
think) suggested mythfilldatabase as a cleanup method. I performed a
mythfilldatabase without any extra commands (make sure it is set up correctly
first), and my database shrunk from 168 to 78MB. Before the cleanup, I was
getting lags on deletes (I put it down to bad fragmentation and ext3, [ext3 is
not meant to fragment but], my partition had been full (100%+) for some time, I
had deleted old small programs and been recording larger programs in their
place). However, after the cleanup the lag has dissapeared, I can now delete
around 10G of data before things start to slow. (Originally I had discounted any
database issue as it was on a seperate spindle to the myth store).</FONT></DIV>
<DIV><FONT face=Arial size=2>Gareth</FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=pvr@groundhog.pair.com href="mailto:pvr@groundhog.pair.com">crs23</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=mythtv-users@mythtv.org
href="mailto:mythtv-users@mythtv.org">mythtv-users@mythtv.org</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Sunday, September 02, 2007 8:02
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> [mythtv-users] Slow MySQL query
after delete</DIV>
<DIV><BR></DIV>
<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>
<P>
<HR>
<P></P>_______________________________________________<BR>mythtv-users mailing
list<BR>mythtv-users@mythtv.org<BR>http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users<BR></BLOCKQUOTE></BODY></HTML>