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 laas.t...@eenet.ee 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: sa...@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.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
