[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

On 11/13/2008 11:40 AM, Pat Pierce wrote:
> 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.
>   

OK, by your saying, "when the system deleted the files out of the 
'deleted' group," you're implying that you deleted the files to the 
Deleted recording group.  If you do, the duplicate flag is automatically 
changed depending on which delete you select (i.e. is enabled/set to 1 
if you say, "Delete," and disabled/set to 0 if you say "Delete and allow 
re-record") when you delete the recording to the Deleted recgroup.  
(From inside the Deleted recgroup, deleting--not saying, "Delete and 
allow re-record"--to remove the file from disk will not flip the switch, 
so whatever you chose when deleting it to the Deleted recgroup will stand.)

There /was/ (a /very/ long time ago) a bug in MythWeb that meant that 
recordings deleted from MythWeb weren't properly marked for duplicate 
detection.
 
> 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,

As there should be.  The recording status for all airings of shows 
matching recording rules in the current program listings as well as some 
history (7 or 10 days--I don't feel like looking right now) is kept for 
reference (i.e. so you can tell why a specific episode didn't record).

>  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);
> From the contents of oldrecorded_test, this appeared to have worked, so I renamed
> "oldrecorded" to "oldrecorded_back", and renamed "oldrecorded_test" to "oldrecorded".

Here, you deleted all sorts of important information.  BTW, Myth 
automatically cleans up this and other tables.

> created, so I moved the backedup table back into place, and my query times
> dropped down under 10 seconds, sometimes as low as 2 seconds.
> My frontend is not hanging like it was, and everything seems better.
>
> Some conclusions and questions:
>
> I am not a database expert, so is there something wrong with the way I copied the
> table as I listed above?
>   

Erm....  Yeah, you broke Myth's data.

> Perhaps some of the other people having issues with slow deletes hanging the frontend
> have done as I did, and have something wrong with the table itself. Is there a way to
>
> check the oldrecorded table for correct structure,

It's done automatically once per day by the backend, so just run the 
backend.

>  other that the "optimize_mythdb.pl"
> script?

That does other stuff, but still useful.  I run it in a daily cron job 
at some time in /very/ early morning (when recordings are unlikely).

>  Is there a way to delete and recreate this table as it should be, as a last
> resort for those who have no backup and can't get their frontend to work?

Basically, the standard answer applies:  don't mess with the DB data.  
(No matter how much you think you understand the schema/data/constraints 
upon that data, Myth understands it much better.)  And remember that 
Myth cleans up all the old/unnecessary data automatically for you, so no 
need to mess with the data.

Mike