[mythtv] Housekeeping function thrashing mysql

[Buechler, Mark R]

I've logged a select sql which is causing mysql to jump to 100% for upwards
of 40 seconds which kills anything else needing data. I believe the sql is
part of the housekeeping thread. Is there anyway of putting a limit on the
return and putting it in a loop instead of getting the whole thing at once?

 

This bit of sql returns 1500 rows in my setup and takes generally 35
seconds. I've optimized the table and rebuilt the index:

 

# Time: 060113 18:07:06

# User at Host: mythtv[mythtv] @ localhost []

# Query_time: 35  Lock_time: 0  Rows_sent: 1500  Rows_examined: 4307708

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.preference 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     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;






EMAIL DISCLAIMER 

Please Note: The information contained in this message may be privileged and
confidential, protected from disclosure, and/or intended only for the use of
the individual or entity named above. If the reader of this message is not
the intended recipient, or an employee or agent responsible for delivering
this message to the intended recipient, you are hereby notified that any
disclosure, distribution, copying or other dissemination of this
communication is strictly prohibited. If you received this communication in
error, please immediately reply to the sender, delete the message and
destroy all copies of it.

Thank You
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-dev/attachments/20060113/71b1b32b/attachment.htm