[mythtv] Scheduler needs table keys?

[Paul Andreassen]

On Sun, 28 Jan 2007 03:21 pm, Brian Schott wrote:
> Oops, I stand corrected.  There are indexes on the title and
> description tables.  Have you rebuilt the database?  This is what
> Debian does, but I don't think the cron job comes with fedora packages.

snip

> more /etc/cron.weekly/mythtv-database
> #!/bin/sh
> # /etc/cron.weekly/mythtv-database script - check and backup
> mythconverg tables
> # Copyright 2005/12/02 2006/10/08 Paul Andreassen

snip

> /usr/bin/mysqlcheck $DEBIAN -s $DBNAME
>
> /usr/bin/savelog -c 7 -l -n -q $BACKUP
>
> /usr/bin/mysqldump $DEBIAN $OPTIONS $DBNAME | gzip > $BACKUP
>
> /usr/bin/logger -p daemon.info -i -t${0##*/} "$DBNAME checked and
> backedup."
>
> # End of file.
>

Hi Brian,

I'm pretty sure that mysqlcheck only checks the tables and not optimises them.  
I'm also a firm believer in not messing with working tables.  Database are 
built for stability and speed.  Optimising tables only removes excess space, 
etc from them and shouldn't produce significate speedups.  This is why 
my /etc/cron.weekly/mythtv-database script found in the debian packages only 
checks and backups.


Alright here is a quick run down of changes I could suggest to improve 
schedule time for 0.20fixes.  This is very rough and would need testing to 
prove any advantage.  The best keys to try would be the two for 
Scheduler::UpdateMatches.


void Scheduler::BuildNewRecordsQueries(int recordid, QStringList &from, 

sort by search / RecSearchType
    query = QString("SELECT recordid,search,subtitle,description "
                    "FROM %1 WHERE search <> %2 AND "
                    "(recordid = %3 OR %4 = -1) ")
        .arg(recordTable).arg(kNoSearch).arg(recordid).arg(recordid);
replace with
    query = QString("SELECT recordid,search,subtitle,description "
                    "FROM %1 WHERE search <> %2 AND "
                    "(recordid = %3 OR %4 = -1) ORDER BY search")
        .arg(recordTable).arg(kNoSearch).arg(recordid).arg(recordid);

key on RECTABLE.recordid, RECTABLE.search
        QString s("RECTABLE.search = :NRST AND "
                 "(RECTABLE.recordid = :NRRECORDID OR :NRRECORDID = -1) AND "

Is this replaced with progfindid?
        case kManualSearch:
            UpdateManuals(result.value(0).toInt());


void Scheduler::UpdateMatches(int recordid) {

key on channel.chanid, channel.visible (move to ON), channel.callsign
"FROM (RECTABLE, program INNER JOIN channel "
"      ON channel.chanid = program.chanid) ") + fromclauses[clause] + QString(
" WHERE ") + whereclauses[clause] + QString(" AND channel.visible = 1 AND "
...
" ((RECTABLE.station = channel.callsign) " // channel matches
replace with
"FROM (RECTABLE, program INNER JOIN channel "
"      ON (channel.chanid = program.chanid AND channel.visible = 1) ) ") + 
fromclauses[clause] + QString(
" WHERE ") + whereclauses[clause] + QString(" AND "
...
" ((RECTABLE.station = channel.callsign) " // channel matches

key on program.chanid, program.manualid, program.title
"FROM (RECTABLE, program INNER JOIN channel "
"      ON channel.chanid = program.chanid) ") + fromclauses[clause] + QString(
... (BuildNewRecordsQueries)
                 "program.manualid = 0 AND "
                 "program.title = RECTABLE.title ");


void Scheduler::AddNewRecords(void) 
{

" INNER JOIN channel ON (channel.chanid = program.chanid) "
maybe change to  (probably optimised out)
" INNER JOIN channel ON (recordmatch.chanid = channel.chanid) "

key on cardinput.sourceid
" INNER JOIN cardinput ON (channel.sourceid = cardinput.sourceid) "

BUG 1000 != ABC
" LEFT JOIN oldrecorded as oldrecstatus ON "
"  ( oldrecstatus.station = channel.callsign AND "
"    oldrecstatus.starttime = program.starttime AND "
"    oldrecstatus.title = program.title ) "
should be (is it needed at all, maybe should match on callsigns)
" LEFT JOIN oldrecorded as oldrecstatus ON "
"  ( oldrecstatus.station = channel.chanid AND "
"    oldrecstatus.starttime = program.starttime AND "
"    oldrecstatus.title = program.title ) "

key on RECTABLE.recordid, RECTABLE.dupmethod
" INNER JOIN RECTABLE ON (recordmatch.recordid = RECTABLE.recordid) "
...
"    RECTABLE.dupmethod > 1 AND "

key on oldrecorded.title, oldrecorded.duplicate, oldrecorded.programid
" LEFT JOIN oldrecorded ON "
...
"    oldrecorded.duplicate <> 0 AND "
"    program.title = oldrecorded.title "
...
"       AND program.programid = oldrecorded.programid) "

key on oldrecorded.title, oldrecorded.duplicate, oldrecorded.findid
" LEFT JOIN oldrecorded ON "
...
"    oldrecorded.duplicate <> 0 AND "
"    program.title = oldrecorded.title "
...
"      (oldrecorded.findid <> 0 AND "
"        oldrecorded.findid = ") + progfindid + QString(") "

key on oldrecorded.title, oldrecorded.duplicate, oldrecorded.subtitle
" LEFT JOIN oldrecorded ON "
...
"    oldrecorded.duplicate <> 0 AND "
"    program.title = oldrecorded.title "
...
"          AND program.subtitle = oldrecorded.subtitle)) "

key on oldrecorded.title, oldrecorded.duplicate, oldrecorded.description
" LEFT JOIN oldrecorded ON "
...
"    oldrecorded.duplicate <> 0 AND "
"    program.title = oldrecorded.title "
...
"          AND program.description = oldrecorded.description)) "

key on program.chanid, program.starttime, program.manualid, program.programid, 
program.generic, program.subtitle
" INNER JOIN program ON (recordmatch.chanid = program.chanid AND "
"                        recordmatch.starttime = program.starttime AND "
"                        recordmatch.manualid = program.manualid) "
...
"      (program.programid <> '' AND program.generic = 0 "
"       AND program.programid = oldrecorded.programid) "
...
"       (((RECTABLE.dupmethod & 0x02) = 0) OR (program.subtitle <> '' "
"          AND program.subtitle = oldrecorded.subtitle)) "

key on program.chanid, program.starttime, program.manualid, program.programid, 
program.generic, program.description
" INNER JOIN program ON (recordmatch.chanid = program.chanid AND "
"                        recordmatch.starttime = program.starttime AND "
"                        recordmatch.manualid = program.manualid) "
...
"      (program.programid <> '' AND program.generic = 0 "
"       AND program.programid = oldrecorded.programid) "
...
"       (((RECTABLE.dupmethod & 0x04) = 0) OR (program.description <> '' "
"          AND program.description = oldrecorded.description)) "

key on recorded.title, recorded.recgroup, recorded.duplicate, recorded.findid
" LEFT JOIN recorded ON "
...
"    recorded.duplicate <> 0 AND "
"    program.title = recorded.title AND "
"    recorded.recgroup <> 'LiveTV' "
...
"      (recorded.findid <> 0 AND "
"        recorded.findid = ") + progfindid + QString(") "

key on recorded.title, recorded.recgroup, recorded.duplicate, 
recorded.programid, recorded.subtitle
" LEFT JOIN recorded ON "
...
"    recorded.duplicate <> 0 AND "
"    program.title = recorded.title AND "
"    recorded.recgroup <> 'LiveTV' "
...
"       AND program.programid = recorded.programid) "
...
"       (((RECTABLE.dupmethod & 0x02) = 0) OR (program.subtitle <> '' "
"          AND program.subtitle = recorded.subtitle)) "

key on recorded.title, recorded.recgroup, recorded.duplicate, 
recorded.programid, recorded.description
" LEFT JOIN recorded ON "
...
"    recorded.duplicate <> 0 AND "
"    program.title = recorded.title AND "
"    recorded.recgroup <> 'LiveTV' "
...
"       AND program.programid = recorded.programid) "
...
"       (((RECTABLE.dupmethod & 0x04) = 0) OR (program.description <> '' "
"          AND program.description = recorded.description)) "

do we need this
" ORDER BY RECTABLE.recordid DESC "


Thanks,
Paul
--