[mythtv-users] [PATCH] Full table scan on program table
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