[mythtv-users] Slow MySQL query after delete

[David Rees]

On 9/5/07, David Brodbeck <gull at gull.us> wrote:
> On Sep 5, 2007, at 1:22 AM, David Rees wrote:
> So it's still there, it's just hidden.  I guess if MySQL (or MythTV)
> quietly ran an OPTIMIZE TABLE once a week, instead of my scheduling
> it in cron, people would stop complaining that MySQL is a "toy"
> database -- even though the behavior would be exactly the same. ;)

The perfect database would always be 100% optimized without the need
to run any "optimize" scripts - it would maintain perfect statistics
with every change in data. Unfortunately, this means that you would
have to incur some amount of overhead for every insert/update/delete
operation when most of the time these operations don't change the
statistics enough to require you to reanalyze the table. So most
databases either rely on the administrator to periodically analyze the
tables or they wait until some percentage of the data of a table has
changed and then automatically analyze the table.

With MySQL, this means you should periodically ANALYZE your tables
when the content of them changes enough to possibly alter query plans.
With Postgres, this means you should VACUUM ANALYZE your tables when
the content of them changes enough.

Unfortunately with MySQL (MyISAM tables), analyzing a table requires a
read lock which will block any updates/inserts/deletes to that table
until the analyze is complete. Postgres with it's MVCC (Multi-Version
Concurrency Control) design lets you run analyze in the background
without blocking any activity. Of course, performance may slow down
while the vacuum is running due to the additional IO/CPU load required
to scan a table, but there's no getting around that.

All databases are the same in this regard, tables need to be analyzed
to generate statistics so that the query optimizer can choose the best
indexes or perform table scans when running queries. Some are able to
do it automatically for you, some are not. Some require that all
insert/update/delete activity halt while the analyze is being done,
some do not.

-Dave