[mythtv] Scheduler needs table keys?

[Brian Schott]

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.

mysql> show indexes from oldrecorded;
+-------------+------------+-------------+--------------+------------- 
+-----------+-------------+----------+--------+------+------------ 
+---------+
| Table       | Non_unique | Key_name    | Seq_in_index | Column_name  
| Collation | Cardinality | Sub_part | Packed | Null | Index_type |  
Comment |
+-------------+------------+-------------+--------------+------------- 
+-----------+-------------+----------+--------+------+------------ 
+---------+
| oldrecorded |          0 | PRIMARY     |            1 | station      
| A         |          77 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          0 | PRIMARY     |            2 | starttime    
| A         |        1392 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          0 | PRIMARY     |            3 | title        
| A         |        1392 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | endtime     |            1 | endtime      
| A         |        1392 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | title       |            1 | title        
| A         |         116 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | seriesid    |            1 | seriesid     
| A         |          53 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | programid   |            1 | programid    
| A         |        1392 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | recordid    |            1 | recordid     
| A         |         116 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | recstatus   |            1 | recstatus    
| A         |           7 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | recstatus   |            2 | programid    
| A         |        1392 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | recstatus   |            3 | seriesid     
| A         |        1392 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | recstatus_2 |            1 | recstatus    
| A         |           7 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | recstatus_2 |            2 | title        
| A         |         198 |     NULL | NULL   |      | BTREE       
|         |
| oldrecorded |          1 | recstatus_2 |            3 | subtitle     
| A         |        1392 |     NULL | NULL   |      | BTREE       
|         |
+-------------+------------+-------------+--------------+------------- 
+-----------+-------------+----------+--------+------+------------ 
+---------+

---

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

# Deleting the MySQL database:
# NOTE: Performing this step will remove the entire database. You  
will lose all
# of your settings and will need to re-run the /usr/share/mythtv/sql/ 
mc.sql
# script to setup the database structure before running the setup  
program.
# $ mysql -u mythtv -p 'drop database mythconverg'
# To restore: (assuming that you've dropped the database)
# $ mysql -u mythtv -p 'create database mythconverg'
# $ zcat /var/backups/mythconverg.sql.gz | mysql -u mythtv -p  
mythconverg
# see http://mythtv.org/docs/mythtv-HOWTO.html#toc23.5

set -e -u

if [ -f /etc/mythtv/mysql.txt ]; then
   . /etc/mythtv/mysql.txt
fi

DBNAME="mythconverg"
BACKUP="/var/backups/$DBNAME.sql.gz"
DEBIAN="--defaults-extra-file=/etc/mysql/debian.cnf"
OPTIONS="--all --complete-insert --extended-insert --quick --quote- 
names --lock-tables"

/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.

---


On Jan 27, 2007, at 11:56 PM, Chris Pinkham wrote:

> Yeah, should have given stats for that also.  My oldrecorded table
> has 8773 items in it, my program table has 29853.  I truncated
> the oldrecorded table on my dev box and that last test of 3
> seconds now runs in 1.3 seconds.  I've been using Myth for over
> 4 years now, so my oldrecorded is a bit full. :)