[mythtv-users] Slow MySQL query after delete

Pat Pierce raker1000 at gmail.com
Thu Nov 13 16:40:26 UTC 2008


I started having issues with horribly slow sql queries after i messed around
with the oldrecorded table. I was able to fix it by restoring my backedup table.

Details of how I caused the problem:
I had set the frontend to move deleted shows into the "deleted" group
rather than
delete them outright, and I was having shows get re-recorded over and over. With
much digging in this list, I found out about the "duplicate" column in
the oldrecorded
table. Evidently by setting them to autoexpire instead of delete, the
"duplicate" setting
was being left at "0" when the system deleted the files out of the
"deleted" group.
After updating the table with "update oldrecorded set duplicate='1';",
I decided to clean up all the duplicate entries in the table. I had
several hundred
lines which were duplicate title and subtitle, so I copied the table to
"oldrecorded_test"
  create table oldrecorded_test as select * from oldrecorded;
and made a delete statement as follows:
  delete from oldrecorded_test T1 where starttime not in
  select max (starttime) from oldrecorded_test T2 where
T2.programid=T1.programid);


More information about the mythtv-users mailing list