[mythtv] FW: RE: [mythtv-users] [PATCH] Full table scan on program table

Sun Sep 5 19:19:05 EDT 2004

 Patch against dbcheck.cpp attached.

Brian

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

I can resubmit this as a proper diff against dbcheck.cpp if that is preferable. I know CVS is inaccessible right now, I will check on it later before resubitting this as an appropriate patch. Brian --- On Sun 09/05, Brian Scully < brian_scully at excite.com > wrote: From: Brian Scully [mailto: brian_scully at excite.com] To: mythtv-dev at mythtv.org Date: Sun, 5 Sep 2004 14:17:41 -0400 (EDT) Subject: [mythtv] FW: RE: [mythtv-users] [PATCH] Full table scan on program table I sent this db patch to user instead of dev, sorry about that. Brian --- 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 13:36:01 -0400 (EDT) Subject: RE: [mythtv-users] [PATCH] Full table scan on program table 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. HTH, Brian --- 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 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 b! etter. 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 timi
ng 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 cha nnel.channum = "501" AND starttime < 20040905105650 AND endtime > 20040905105650 AND program.c! hanid = channel.chanid AND channel.sourceid = cardinput.sourceid AND cardinput.cardid = capturecard.cardid ! AND capturecard.cardid = "1" AND capture card.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! _! ______________________________________________ mythtv-users mailing list mythtv-users at mythtv.org http://mythtv.org/cgi-b
in
 /mailman/listinfo/mythtv-users _______________________________________________ Join Excite! - http://www.excite.com The most personalized portal on the Web! _______________________________________________ mythtv-users mailing list mythtv-users at mythtv.org http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users _______________________________________________ Join Excite! - http://www.excite.com The most personalized ! portal on the Web! _______________________________________________< br >mythtv-dev mailing list mythtv-dev at mythtv.org http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev _______________________________________________ Join Excite! - http://www.excite.com The most personalized portal on the Web! _______________________________________________ mythtv-dev mailing list mythtv-dev at mythtv.org http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev

_______________________________________________
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
-------------- next part --------------
A non-text attachment was scrubbed...
Name: dbcheck.diff
Type: application/octet-stream
Size: 895 bytes
Desc: not available
Url : http://mythtv.org/pipermail/mythtv-dev/attachments/20040905/0d92661a/dbcheck.obj