On 17 Feb 2009, at 10:52 pm, Jesse Vincent wrote: >> 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.
OK, I've just moved up to 5.0.51a, as supplied in etch-backports, and the problem persists. An ANALYZE TABLE on the Attachments table has helped a bit with the ludicrous statistics from EXPLAIN, but they're now just very silly rather than ludicrous: 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 | 2324446 | Using index | +----+-------------+-------------+-------+---------------+--------- +---------+------+---------+-------------+ A reload might be the way forward here. There is a part of me wondering what suddenly broke this today. I'm currently searching the table for suspiciously large looking attachments. 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: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
