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: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
