[mythtv] innodb vs myism preformance

[Daniel Manjarres]

Adding the index didn't change much, (isn't that a primary key
anyway?), but recordid was int(11) in the recorded table instead of
being int(10). Changing it to int(10) and running OPTIMIZE on the
table drop the query down to 4.75 seconds.

I'll switch everything back to the default 0.17 and OPTIMIZE to see
how big the improvements really are.

[
mysql> DROP INDEX title ON recorded;
Query OK, 747 rows affected (0.25 sec)
Records: 747  Duplicates: 0  Warnings: 0

mysql> DROP INDEX findid ON recorded;
Query OK, 747 rows affected (0.22 sec)
Records: 747  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE recorded CHANGE recordid recordid int(11) default NULL;
Query OK, 747 rows affected (0.24 sec)
Records: 747  Duplicates: 0  Warnings: 0

mysql> OPTIMIZE TABLE recorded;
+----------------------+----------+----------+----------+
| Table                | Op       | Msg_type | Msg_text |
+----------------------+----------+----------+----------+
| mythconverg.recorded | optimize | status   | OK       |
+----------------------+----------+----------+----------+
1 row in set (0.01 sec)

mysql> ANALYZE TABLE recorded;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| mythconverg.recorded | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.00 sec)
]

run the query:  3019 rows in set (15.95 sec)

[
CREATE INDEX title ON recorded (title);
]

run the query: 3019 rows in set (4.78 sec)

[
CREATE INDEX findid ON recorded (findid);
]

run the query: 3019 rows in set (4.72 sec)

[
ALTER TABLE recorded CHANGE recordid recordid int(10) default NULL;
OPTIMIZE TABLE recorded;
ANALYZE TABLE recorded;
]

run the query: 3019 rows in set (4.73 sec)

[
sitch to myisam
for a in $(ls *frm | cut -f1 -d. ); do echo "use mythconverg; ALTER
TABLE $a TYPE=MYISAM; ANALYZE TABLE $a; OPTIMIZE TABLE $a;"|mysql ;
done
]

run the query: 3019 rows in set (4.95 sec)

[
revert to 0.17 defalts
]

run the query: 3019 rows in set (18.49 sec)

[
for verification switch to all the new crap and innodb
]

run the query: 3019 rows in set (5.27 sec)
OPTIMIZE, ANALYZE
run the query: 3019 rows in set (5.51 sec)

Ok, so switching OPMTIMZE and co are not miracle workers, but I think
this is enough work for me for now.