I believe you're on mysql 5.1.x, it's know issue with some 5.1.2x. I can not say if it's been fixed in recent release of 5.1. You should file bug into mysql bug tracker.
On Wed, May 27, 2009 at 1:16 AM, Elijah Wright <[email protected]> wrote: > Folks, > We're in the midst of migrating from RT 3.6.4 to RT 3.8.2, and having some > "interesting" issues with the Attachments table. > Here's what queries against Attachments looked like on our old RT instance: > previous RT install (3.6.4): > mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS > NOT NULL AND main.Content != '') AND (main.Parent = '1208717') AND > (main.ContentType = 'text/plain') ORDER BY main.id ASC; > +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ > | 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 | > const | 2 | Using where; Using filesort | > +----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ > 1 row in set (0.00 sec) > mysql> > > Now, here's what (what should be the self-same queries...) is being produced > on our new RT host, running 3.8.2: > new RT host: > mysql> explain SELECT main.* FROM Attachments main WHERE (main.Content IS > NOT NULL AND main.Content != '') AND (main.Parent = '1208717') AND > (main.ContentType = 'text/plain') ORDER BY main.id ASC; > +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref > | rows | Extra | > +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ > | 1 | SIMPLE | main | index | Attachments3 | PRIMARY | 4 | > NULL | 2199950 | Using where | > +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ > 1 row in set (0.11 sec) > mysql> > Obviously, on the new host, it isn't using the index on the Attachments > table - and that's REALLY making things go slowly for some bits of RT. > Anybody got a clue how to fix this, or an idea of what we can do to coerce > it to use the proper index? > [We have quite a lot of tickets, and a fairly scary amount of spam has > leaked into our RT instance - I'm going to need to run the shredder on a > large number of deleted tickets, but the Attachments table is currently so > big that doing so is a bit daunting...] > This is on a new host, and we imported via a mysqldump and re-import, so the > data in the table should be defragmented... but the end result is clearly > problematic. > thanks in advance, > --elijah > > _______________________________________________ > 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 > -- Best regards, Ruslan. _______________________________________________ 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
