[rt-users] ticket taking a long time to draw

[Ruslan Zakirov]

   Hello, George.

To fix this performance issue you need to create two indexes:
CREATE INDEX LocalUsers1 ON Users(EmailAddress);
CREATE INDEX LocalCGM1 ON CachedGroupMembers(MemberId,GroupId,Disabled);

Please, after each create operation run explain and send me results.

On 5/23/06, George Barnett <george at alink.co.za> wrote:
> Hi,
>
> Tickets are taking a very long time to draw on our RT install.  After a
> bit of digging, I've found it's the 'More about XYZ' box that lists
> other tickets the user has open.
>
> This query:
>
> SELECT DISTINCT main.* FROM (((Tickets main  JOIN Groups Groups_1  ON (
> Groups_1.Instance = main.id))  LEFT JOIN CachedGroupMembers
> CachedGroupMembers_2  ON ( CachedGroupMembers_2.GroupId = Groups_1.id)
> AND( (CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId)))
> LEFT JOIN Users Users_3  ON ( Users_3.id =
> CachedGroupMembers_2.MemberId))   WHERE ((Groups_1.Domain =
> 'RT::Ticket-Role')) AND ((Groups_1.Type = 'Requestor')) AND
> ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
> ((main.Type = 'ticket')) AND ( (  ( (Users_3.EmailAddress =
> 'foo.bar at test.com') )  ) AND ( (main.Status = 'new')OR(main.Status =
> 'open') ) )  ORDER BY main.Priority DESC  LIMIT 10;
>
> We have around 800K tickets in the database and this is causing the
> select to take about 16 seconds.  The DB server isn't small either, it's
> a 4 way opteron.
>
> after doing an explain select, I noticed the row estimate of 230K
> +----+-------------+----------------------+--------+-----------------+------------+---------+-----------------------------------+--------+----------------------------------------------+
> | id | select_type | table                | type   | possible_keys   |
> key        | key_len | ref                               | rows   |
> Extra                            |
> +----+-------------+----------------------+--------+-----------------+------------+---------+-----------------------------------+--------+----------------------------------------------+
> |  1 | SIMPLE      | Groups_1             | ref    | Groups1,Groups2 |
> Groups2    |      65 | const                             | 231336 |
> Using where; Using temporary; Using filesort |
> |  1 | SIMPLE      | main                 | eq_ref | PRIMARY         |
> PRIMARY    |       4 | rt3.Groups_1.Instance             |      1 |
> Using where                            |
> |  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem      |
> DisGrouMem |       5 | rt3.Groups_1.id                   |      1 |
> Using index; Distinct                        |
> |  1 | SIMPLE      | Users_3              | eq_ref | PRIMARY,Users4  |
> PRIMARY    |       4 | rt3.CachedGroupMembers_2.MemberId |      1 |
> Using where; Distinct                        |
> +----+-------------+----------------------+--------+-----------------+------------+---------+-----------------------------------+--------+----------------------------------------------+
>
> Hence I have added an index on Groups(Domain,Type) which has lowered
> this number to 20k, however it still takes ages.
>
> Is there something I'm missing?
>
> rt 3.4.5
> searchbuilder 1.37
>
>
>
>
>
> --
> George Barnett
> Reality Engineer
>
> m: (+44) 797 457 1868
> e: george at alink.co.za
>
> Hello?  Enema Bondage?  I'm calling because I want to be happy, I guess ...
> _______________________________________________
> 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
>
>
> We're hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html
>


-- 
Best regards, Ruslan.