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

[Ruslan Zakirov]

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.