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

[Brian Kerr]

On 7/25/07, Ruslan Zakirov <ruz at bestpractical.com> wrote:

...

> Can you give me EXPLAINs? Wonder if you can give explain of the query
> for both versions of mysql?

The explains are identical between 4.1.18 and 4.1.22:

|  1 | SIMPLE      | main                 | ALL   |
PRIMARY,Tickets2,Tickets4,Tickets5 | NULL       |    NULL | NULL
                    | 141524 | Using where; Using temporary; Using
filesort |
|  1 | SIMPLE      | Groups_1             | ref   | Groups1,Groups2
                | Groups1    |     135 | const,rt3.main.id,const
 |      1 | Using where; Using index; Distinct           |
|  1 | SIMPLE      | Users_3              | index | NULL
                | Users4     |     121 | NULL
 |   1548 | Using where; Using index; Distinct           |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref   | DisGrouMem,GrouMem
                | DisGrouMem |      10 |
rt3.Groups_1.id,rt3.Users_3.id |      1 | Using where; Using index;
Distinct

> Keneth, Robert or other RT users, could you please try to reproduce
> results Brian sees.
>
> >
> > Seems like a pretty huge difference for any possible query optimizer
> > changes from 4.1.18 -> 4.1.21.
> When I'm talking about mysql query optimizer, I'm talking not about
> optimizations mysql uses during execution of a query, but about the
> mysql query planner which defines order of operations, which indexes
> to use and so on. When you run EXPLAIN SELECT... you're executing the
> planner and get a plan mysql will use to execute the query. Even minor
> changes in a plan can kill performance.

Gotcha, this is definately something that was changed in mysql between
4.1.18 and 4.1.22.

> Ok, we have another variant of the query. Remember that third query we
> tested before with COUNT(...)? Try the following query:
> SELECT SQL_NO_CACHE DISTINCT main.*
> FROM Tickets main
>     JOIN Groups Groups_1
>         ON  Groups_1.Domain = 'RT::Ticket-Role'
>         AND Groups_1.Type = 'Requestor'
>         AND Groups_1.Instance = main.id
>     LEFT JOIN CachedGroupMembers CachedGroupMembers_2
>         ON CachedGroupMembers_2.GroupId = Groups_1.id
>     LEFT JOIN Users Users_3
>         ON CachedGroupMembers_2.MemberId = Users_3.id
> WHERE
>     main.Status != 'deleted'
>     AND main.Type = 'ticket'
>     AND main.EffectiveId = main.id
>     AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' )
> ORDER BY main.id ASC LIMIT 50
>
> May be we should switch to this variant even if counting ticket with
> it is slower.

This runs in 2.07 sec in 4.1.22 and 2.10 sec in 4.1.18.

-Brian