On 7/25/07, Ruslan Zakirov <[EMAIL PROTECTED]> 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
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
