> OK, OPTIMIZE TABLE done, but performance on SELECT COUNT(id) FROM > Attachments is still terrible: > > This does sort of smell like an optimiser bug - it says it's using > indices, but the level of I/O being done by the machine suggests it's > actually performing a full table scan.
Indeed. Before you dig deeply into what's going on with this version of mysql, coming up to a current 5.0.x probably makes sense. > > mysql> optimize table Attachments; > +------------------+----------+----------+----------+ > | Table | Op | Msg_type | Msg_text | > +------------------+----------+----------+----------+ > | rtdb.Attachments | optimize | status | OK | > +------------------+----------+----------+----------+ > 1 row in set (23 min 11.29 sec) > > mysql> select count(id) from Attachments; > +-----------+ > | count(id) | > +-----------+ > | 806180 | > +-----------+ > 1 row in set (3 min 27.42 sec) > > mysql> explain select count(id) from Attachments; > +----+-------------+-------------+-------+---------------+--------- > +---------+------+---------+-------------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > +----+-------------+-------------+-------+---------------+--------- > +---------+------+---------+-------------+ > | 1 | SIMPLE | Attachments | index | NULL | PRIMARY | 4 > | NULL | 3065551 | Using index | > +----+-------------+-------------+-------+---------------+--------- > +---------+------+---------+-------------+ > 1 row in set (0.00 sec) > > Now, I may be being silly here, but how can a table with only 806180 > id's in its primary key have more than 3 million rows? An excellent question. I have a sneaking suspicion there's some DB corruption in there somewhere :/ But maybe it's just a database bug. --
pgpVYLSbjmqrM.pgp
Description: PGP signature
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com