[mythtv-users] Slow MySQL query after delete

[Pat Pierce]

Well, that sounds likely. I have not had much db experience, so
I did not know about the indexes. I'll look into how to index, and
into the EXPLAIN command.

Y'know, I am learning a lot about databases with mythtv, and its
very applicable to certain parts of my job. This just proves that
TV is good for your mind. ;)

Thanks, Pat

On Fri, Nov 14, 2008 at 8:29 PM, Matthew McClement <mythtv at macker.co.uk>wrote:

> Pat Pierce wrote:
> *snip*
> >
> > 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
> *snip*
>
> Unless I'm missing something, it doesn't look like you re-created the
> indexes on the new table, so selects were likely resulting in a full
> table scan.
>
> Depending on how much time you have, you can usually figure out what's
> causing the performance problems by turning on slow query logging in
> MySQL and then running the queries manually, prefixed with EXPLAIN. The
> output isn't hugely complicated but the manual is your best reference to
> figuring out the
> output(http://dev.mysql.com/doc/refman/5.0/en/using-explain.html).
>
> Matt
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20081114/e86c653a/attachment.htm