[rt-users] mis-use of indexes on the Attachments table (RT 3.8.2)

[Elijah Wright]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090526/545a246c/attachment.htm>