[mythtv] Scheduler needs table keys?

[Paul Andreassen]

On Sun, 28 Jan 2007 10:32 pm, Paul Andreassen wrote:
> 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);

Puts all search type together to hopefully to hit the same tables.

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

maybe order should be swaped because recordid is optional
CREATE INDEX sr ON record (search, recordid);

What other RECTABLE is there?

> 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

INNER JOIN means each and every row in the first table is joined to each and 
every row in the second table
CREATE INDEX cvc ON channel (chanid, visible, callsign);

> 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 ");

only selected programs are joined
CREATE INDEX mt ON program (manualid, 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) "

CREATE INDEX sourceid ON 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 "

CREATE INDEX rd ON record (recordid, dupmethod);

> 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) "

CREATE INDEX tdp ON oldrecorded (title, duplicate, 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(") "

CREATE INDEX tdf ON oldrecorded (title, duplicate, findid);

> 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)) "

CREATE INDEX tds ON oldrecorded (title, duplicate, 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)) "

CREATE INDEX tdd ON oldrecorded (title, duplicate, description(128));

> 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)) "

CREATE INDEX csmgps ON program (chanid, starttime, manualid, generic, 
programid, 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)) "

CREATE INDEX csmgpd ON program (chanid, starttime, manualid, generic, 
programid, description(128));

> 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(") "

CREATE INDEX trdf ON recorded (title, recgroup, duplicate, findid);

> 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)) "

CREATE INDEX trdps ON recorded (title, recgroup, duplicate, programid, 
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)) "

CREATE INDEX trdpd ON recorded (title, recgroup, duplicate, programid, 
description(128));

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

Indexes are added:
only ones you really need
column most used first
more duplicates first for many column
index on prefix (must for text and blob)
FULLTEXT only MyISAM and only CHAR, VARCHAR and TEXT
You cannot use CREATE INDEX to create a primary key index. Use ALTER TABLE 
instead.

Thanks,
Paul
--