[rt-users] Re: [PERFORMANCE][PATCH] Searches by watchers with OR aggregator

Robert Long rlong at bluegecko.net
Wed Jul 25 22:26:45 EDT 2007


First off thanks for the patch, I just applied it and the effected
queries are running faster!

As for the query below:

On 5.0.45 I'm getting:
Original query:  0.23 sec
New query:  0.59 sec

We don't really have any older 4.x systems laying around that I could
test this on. 

Explains are listed below, thanks again!

.r'


Original query:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: ALL
possible_keys: PRIMARY,Tickets2,Tickets3,Tickets4,Tickets5,Tickets6
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7275
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Groups_1
         type: ref
possible_keys: Groups1,Groups2
          key: Groups1
      key_len: 139
          ref: const,rt3.main.id,const
         rows: 1
        Extra: Using where; Using index; Distinct
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: Users_3
         type: index
possible_keys: NULL
          key: Users4
      key_len: 123
          ref: NULL
         rows: 622
        Extra: Using where; Using index; Distinct
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: CachedGroupMembers_2
         type: ref
possible_keys: DisGrouMem,GrouMem
          key: GrouMem
      key_len: 10
          ref: rt3.Groups_1.id,rt3.Users_3.id
         rows: 1
        Extra: Using where; Using index; Distinct



New  Query:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: ALL
possible_keys: PRIMARY,Tickets2,Tickets3,Tickets4,Tickets5,Tickets6
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7275
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Groups_1
         type: ref
possible_keys: Groups1,Groups2
          key: Groups1
      key_len: 139
          ref: const,rt3.main.id,const
         rows: 1
        Extra: Using where; Using index; Distinct
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: CachedGroupMembers_2
         type: ref
possible_keys: DisGrouMem,GrouMem
          key: DisGrouMem
      key_len: 5
          ref: rt3.Groups_1.id
         rows: 1
        Extra: Using index; Distinct
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: Users_3
         type: eq_ref
possible_keys: PRIMARY,Users3
          key: PRIMARY
      key_len: 4
          ref: rt3.CachedGroupMembers_2.MemberId
         rows: 1
        Extra: Using where; Distinct





More information about the rt-users mailing list