[mythtv-users] Slow MySQL query after delete

[David Rees]

On 9/4/07, Andrew Burgess <aab at cichlid.com> wrote:
> >When I run that SELECT in mysql it consistently returns 3694 rows in around
> >31 seconds.  I had run optimize_mythdb.pl.  Running it again had no effect.
>
> I don't know jack about mysql. But here is what I would try
> from a general troubleshooting point of view.
>
>   time mysql <args to make it run the query>

That won't work because you are timing the client and the bulk of the
time will be spent on the server process.

What can help is watching vmstat/top on the server while running the
query to see what mysql is doing while the query is running as well as
looking at the explain output (which is what the OP has already done.

The OP didn't mention what version of MySQL he's running or how he's
tuned MySQL, but both of those items could significant affect his
performance.

Frankly, I'm not surprised that MySQL has a hard time generating
consistent results with this type of query and wish that the
developers would have used some database abstraction or at least
something that would allow other database backends to be plugged in so
we wouldn't be locked into MySQL.

If they are heading down the path of embedding MySQL into the backend,
I'm not sure why something like SQLite hasn't been considered which
has been designed from the start to be embedded?

-Dave