[mythtv-users] [PATCH] Full table scan on program table

Brian Scully brian_scully at excite.com
Sun Sep 5 13:36:01 EDT 2004

I've been tweaking this a bit, and i've got another perceptible boost, although much smaller than the last one - it rids this query of all full table scans.

ALTER TABLE channel ADD_INDEX channelnum (channum, sourceid);

This should probably be a unique index, but i've got some duplicate channums - maybe due to upgrades, I don't know.  I'm gonna do a little research and maybe pull out the dupes.  Sourceid isn't necessary for me (i've only got one source), but it's probably better to be generic in this case.


 --- On Sun 09/05, Brian Scully < brian_scully at excite.com > wrote:
From: Brian Scully [mailto: brian_scully at excite.com]
To: mythtv-users at mythtv.org
Date: Sun,  5 Sep 2004 12:12:02 -0400 (EDT)
Subject: [mythtv-users] [PATCH] Full table scan on program table

<br>First of all, thanks to Isaac and the mythtv community for making such a great application.  It's fundamentally changed the way I watch TV ... for the better.<br><br>DISCLAIMER: I am not a Database or MySQL expert.  This solution works for me, YMMV.<br><br>I have been having lots of trouble lately with the frontend timing out the socket connection to the backend because it thinks that the backend dies.  This happens (almost) any time I pull up the program guide or go into channel browse mode.  I turned on mysql slow query logging, and did explain plans on the queries there.  The following query takes ~13 sec on my machine:<br><br>SELECT starttime,endtime,title,subtitle,description,category,callsign,icon,channel.chanid, seriesid, programid <br>              FROM program,channel,capturecard,cardinput <br>              WHERE channel.channum = "501" <br>              AND starttime < 20040905105650 <br>              AND endtime > 20040905105650 <br>              AND program.chanid = channel.chanid <br>              AND channel.sourceid = cardinput.sourceid <br>              AND cardinput.cardid = capturecard.cardid <br>              AND capturecard.cardid = "1" <br>              AND capturecard.hostname = "htpc";<br><br>The explain plan tells me that it is doing a full table scan on the program table (~94k records) for this query.  An index including chanid, starttime, and endtime is necessary. There are two indexes that are close: the primary key(chanid, starttime), and an index on endtime - but unfortunately mysql only uses one index for a query.<br><br>So I added an index called "program_guide" including the three necessary columns, although perhaps the mythtv maintainers would prefer to add endtime to the primary key.<br><br>ALTER TABLE program ADD INDEX program_guide (chanid,starttime,endtime);<br><br>With this change, the table scan has moved to channel - but there's only 340 records in that table so there's not much of a performance hit.  My queries have gone from 13s to << 1s.<br><br>HTH,<br>Brian<br><br>
_______________________________________________<br>Join Excite! - http://www.excite.com<br>The most personalized portal on the Web!<br>_______________________________________________<br>mythtv-users mailing list<br>mythtv-users at mythtv.org<br>http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users<br>

Join Excite! - http://www.excite.com
The most personalized portal on the Web!

More information about the mythtv-users mailing list