Laas, This SQL is too heavy and incorrect, RT 3.8.6 has a fix. On Fri, Oct 23, 2009 at 1:00 PM, Laas Toom <[email protected]> wrote:
> Hello! > > We have RT with ca 50 000 tickets in it, which, I think, has been running > since 3.2 and upgraded lately to 3.8.5. After that I added RT-IR to it and > everything seems to be fine except the Lookup IP query. First of all it > takes ages. MySQL slow log reports query_time in order of 500 to 800 seconds > and rows examined 3 to 4 millions. > > And after I wait through it I get irrelevant data - Incident Reports and > Incidents, which do not have this IP nowhere in them. > > Before I upgraded the RT and installed RTIR to it, I copied the whole > database for testing (that means all the same content in it except newer > tickets) and played around with RTIR a bit. I did not notice the slowness in > the test database and IIRC the virgin production RTIR was fast also. But > search results were irrelevant in test too (though at the time I hoped it > was my testing that messed things up). And lookup in the production DB seems > to grow slower with every Incident Report added. > > Can somebody take a look at this query (taken from slow log) if this seems > correct and why does RT join ObjectCustomFieldValues to itself 4 times and > perform WHERE lookup on different fields in all of them. > > Below are the query and it's explain from phpMyAdmin. > > Best, > Laas > > > SELECT DISTINCT main.* FROM Tickets main JOIN ObjectCustomFieldValues > ObjectCustomFieldValues_3 ON ( ObjectCustomFieldValues_3.CustomField = '14' > ) AND ( ObjectCustomFieldValues_3.Disabled = '0' ) AND ( > ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' ) AND ( > ObjectCustomFieldValues_3.ObjectId = main.id ) JOIN > ObjectCustomFieldValues ObjectCustomFieldValues_4 ON ( > ObjectCustomFieldValues_4.Disabled = '0' ) AND ( > ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket' ) AND ( > ObjectCustomFieldValues_4.CustomField = '14' ) > AND ( ObjectCustomFieldValues_4.ObjectId = main.id ) JOIN > ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( > ObjectCustomFieldValues_1.CustomField = '14' ) AND ( > ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND ( > ObjectCustomFieldValues_1.Disabled = > '0' ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id ) JOIN > Transactions Transactions_5 ON ( Transactions_5.ObjectId = main.id > ) JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2 ON ( > ObjectCustomFieldValues_2.Disabled = '0' ) AND ( > ObjectCustomFieldValues_2.ObjectId = main.id ) AND ( > ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket' ) AND ( > ObjectCustomFieldValues_2.CustomField = > '14' ) WHERE (main.Status != 'deleted') AND (Transactions_5.ObjectType = > 'RT::Ticket') AND ( ( ( ( ( ( ObjectCustomFieldValues_1.Content <= > '193.040.010.218' OR ( ( ObjectCustomFieldValues_1.Content = '' OR > ObjectCustomFieldValues_1.Content IS NULL ) AND > ObjectCustomFieldValues_1.LargeContent <= '193.040.010.218' ) ) ) ) AND > ( ( ( ObjectCustomFieldValues_2.LargeContent >= '193.040.010.218' ) ) ) > AND ( ( ( ObjectCustomFieldValues_3.Content >= '000.000.000.000' OR ( > ( ObjectCustomFieldValues_3.Content > = '' OR ObjectCustomFieldValues_3.Content IS NULL ) AND > ObjectCustomFieldValues_3.LargeContent >= '000.000.000.000' ) ) ) ) AND > ( ( ( ObjectCustomFieldValues_4.LargeContent <= '255.255.255.255' ) ) ) > ) ) AND ( Transactions_5.Created > '2009-08-08 10:56:21' ) ) AND > (main.Type = 'ticket') AND (main.EffectiveId = main.id) > > idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLE > ObjectCustomFieldValues_1ref > TicketCustomFieldValues1,TicketCustomFieldValues2,... > ObjectCustomFieldValues3263const,const,const3832Using where; Using > temporary1SIMPLEObjectCustomFieldValues_3ref > TicketCustomFieldValues1,TicketCustomFieldValues2,... > TicketCustomFieldValues18const,rt3.ObjectCustomFieldValues_1.ObjectId1Using > where1SIMPLEObjectCustomFieldValues_4ref > TicketCustomFieldValues1,TicketCustomFieldValues2,... > TicketCustomFieldValues18const,rt3.ObjectCustomFieldValues_1.ObjectId1Using > where1SIMPLEObjectCustomFieldValues_2ref > TicketCustomFieldValues1,TicketCustomFieldValues2,... > TicketCustomFieldValues18const,rt3.ObjectCustomFieldValues_1.ObjectId1Using > where1SIMPLEmaineq_refPRIMARY,Tickets3,Tickets4,Tickets5,Tickets6PRIMARY4 > rt3.ObjectCustomFieldValues_2.ObjectId1Using where1SIMPLETransactions_5ref > Transactions1Transactions170const,rt3.ObjectCustomFieldValues_1.ObjectId1Using > where; Distinct > > > _______________________________________________ > 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 > -- 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
