[rt-users] RT-IR IP lookup slow

[Ruslan Zakirov]

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.toom at 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: sales at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091023/87e1587c/attachment.htm>