[rt-users] RT-IR IP lookup slow

[Laas Toom]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091023/adeb873b/attachment.htm>