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

[Brian Scully]

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.

DISCLAIMER: I am not a Database or MySQL expert.  This solution works for me, YMMV.

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:

SELECT starttime,endtime,title,subtitle,description,category,callsign,icon,channel.chanid, seriesid, programid 
              FROM program,channel,capturecard,cardinput 
              WHERE channel.channum = "501" 
              AND starttime < 20040905105650 
              AND endtime > 20040905105650 
              AND program.chanid = channel.chanid 
              AND channel.sourceid = cardinput.sourceid 
              AND cardinput.cardid = capturecard.cardid 
              AND capturecard.cardid = "1" 
              AND capturecard.hostname = "htpc";

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.

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.

ALTER TABLE program ADD INDEX program_guide (chanid,starttime,endtime);

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.

HTH,
Brian

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