[mythtv-users] Slow MySQL query after delete

[Dan Wilga]

Running the original query I got:

   1798 rows in set (2.91 sec)

I also ran the EXPLAIN on my DB and did not come up with any 
significant differences.

Normally, when looking at EXPLAINs, the kiss of death is "Using 
temporary; Using filesort". This indicates that MySQL has to create a 
temporary, sometimes disk-based, table to hold the intermediate 
results. If it does have to go to disk, this can be very slow.

But the good news here is that you're only dealing with 2 rows in 
that part of the query, so even if it did have to use a temp table on 
disk, it should be really quick. I suggest you look at your DB 
optimization settings. In /etc/my.conf I've been using these for some 
time:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
max_allowed_packet          = 1M
table_cache                 = 256
sort_buffer_size            = 4M   # was 512K
net_buffer_length           = 8K
read_buffer_size            = 1M   # was 512K
read_rnd_buffer_size        = 1M
key_buffer                  = 16M
query_cache_size            = 16M
-- 
Dan Wilga                                                        "Ook."