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

[Ruslan Zakirov]

http://bugs.mysql.com/bug.php?id=37680 - here is bug report, you can
help debug issue.

On Wed, May 27, 2009 at 1:32 AM, Ruslan Zakirov
<ruslan.zakirov at gmail.com> wrote:
> 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 <elw at brandorr.com> 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: sales at bestpractical.com
>>
>>
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>>
>
>
>
> --
> Best regards, Ruslan.
>



-- 
Best regards, Ruslan.