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)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ObjectCustomFieldValues_1 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
ObjectCustomFieldValues3 263 const,const,const 3832 Using where; Using
temporary
1 SIMPLE ObjectCustomFieldValues_3 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues1 8
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where
1 SIMPLE ObjectCustomFieldValues_4 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues1 8
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where
1 SIMPLE ObjectCustomFieldValues_2 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues1 8
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where
1 SIMPLE main eq_ref PRIMARY,Tickets3,Tickets4,Tickets5,Tickets6
PRIMARY 4 rt3.ObjectCustomFieldValues_2.ObjectId 1 Using where
1 SIMPLE Transactions_5 ref Transactions1 Transactions1 70
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where; Distinct