[rt-users] Certain RT operations painfully slow.

[Raed El-Hames]

Todd,

Another approach is to dissect DeleteWatcher into its row sql and re-write your script to talk sql directly instead of going through the api ... (Its highly not recommended) but with 20 million rows its an option.
The advantages you gain is not having to do as many selects.
You may still get queries lasting > 86s , but I would bet those will be 50% less than the api.

Good luck
Roy 


> -----Original Message-----
> From: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-
> bounces at lists.bestpractical.com] On Behalf Of Todd Chapman
> Sent: 18 January 2011 18:19
> To: Jesse Vincent
> Cc: rt-users
> Subject: Re: [rt-users] Certain RT operations painfully slow.
> 
> On Tue, Jan 18, 2011 at 1:13 PM, Jesse Vincent <jesse at bestpractical.com>
> wrote:
> > Todd,
> >
> >> >> Which causes this long running mysql query:
> >> >>
> >> >>    Time: 86
> >> >>    Info: SELECT main.* FROM CachedGroupMembers main   WHERE
> ((main.Via = '28522070')) AND ((main.id != '28522070'))
> >> >>
> >> >> Can anyone recommend an approach to fixing this problem?
> >> >
> >> > What does an EXPLAIN on that select tell you?
> >>
> >> mysql> explain SELECT main.* FROM CachedGroupMembers main   WHERE
> ((main.Via = '28522070')) AND ((main.id != '28522070'))\G
> >>   select_type: SIMPLE
> >>         table: main
> >>          type: range
> >> possible_keys: PRIMARY
> >>           key: PRIMARY
> >>       key_len: 4
> >>           ref: NULL
> >>          rows: 20568305
> >>         Extra: Using where
> >> 1 row in set (0.02 sec)
> >
> > What happens if you add an index on Via?
> 
> I'm considering that, but with 20 million+ rows it will take a
> non-trivial amount of time.
> 
> >
> > Jesse
> > --
> >