On 17 Feb 2009, at 6:16 pm, Jesse Vincent wrote: > > >> I believe so (Tim did it). >> >>>> # Query_time: 240 Lock_time: 0 Rows_sent: 4 Rows_examined: 27 > > That just looks...crazy. Can you try an OPTIMIZE TABLE Attachments; ? > (It may take ... a while.)
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. 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? And counting them takes three minutes, which really isn't many rows a second... Tim -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE. _______________________________________________ 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