[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