[rt-users] Database indexes

[Ruslan Zakirov]

RT has already bunch of indexes, but recently I found that new index required:
1) CREATE INDEX MyCGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
It helps speed up searches by watchers, something like "requestor.id =
xxx". And other searches when you want to find list of groups an user
is meber of.

Index 1) would be part of feature RT versions, but I don't know when,
you can now create it your self, just after updates check that you
have no duplicated indexes.

When requestor is unprivileged you see "More about user XXX" box on
the ticket page, this search uses user's EmailAddress to search ticket
(TicketSQL "Requestor.EmailAddress = 'foo at bar.com' "). The search is
very unoptimal with previouse index and index on EmailAddress:
2) CREATE INDEX MyUsers1 ON Users(EmailAddress);
This index helps any time you search by users email addresses.

I have a fix that speed up "More about user XXX" box without second
index and the change would be part of 3.4.6 and next 3.6 RC, but
anyway first index is required.

Also if you often use some fields from Users table, for example
Organization. You can index it too.

Also if turn on mysql slow queries log you can send us queries that
take too much time and we'll try to analyze and find solution.

On 5/28/06, Mathew Snyder <msnyder at servervault.com> wrote:
> Has anyone created indexes on their database?  I took a look at the
> tables and can't decide on which columns to index for each one.  If
> someone has already done this and is happy with the results could you
> post your indexes?
>
>
> --
> Mathew Snyder
> Systems Administrator
> Network+
> ServerVault TechOps

-- 
Best regards, Ruslan.