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 <[EMAIL PROTECTED]> 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 = '[EMAIL PROTECTED]') ) ) 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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.
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] 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
