[Rt-devel] bad performance with search

[Roy El-Hames]

Hi Dirk;
Did you get any further/solution with the below, we have similar problem 
with more or less similar query..
my system is rt.3.4.4,DBIx::SearchBuilder v1.33,mysql  4.1.14 ..
sql genertaed:
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Groups Groups_1, 
CachedGroupMembers CachedGroupMembers_2, Users Users_3, Groups Groups_4, 
CachedGroupMembers CachedGroupMembers_5, Users
Users_6, CachedGroupMembers CachedGroupMembers_7  WHERE 
((CachedGroupMembers_2.MemberId = Users_3.id)) AND 
((CachedGroupMembers_5.MemberId = Users_6.id)) AND 
((CachedGroupMembers_7.Membe
rId = Users_6.id)) AND ((Groups_1.Domain = 'RT::Ticket-Role')) AND 
((Groups_1.id = CachedGroupMembers_2.GroupId)) AND ((Groups_4.id = 
CachedGroupMembers_5.GroupId)) AND ((main.EffectiveId
= main.id)) AND ((main.Status != 'deleted')) AND ((main.id = 
Groups_1.Instance)AND(main.id = Groups_4.Instance)) AND ( ( (main.Queue 
= '116')OR(main.Owner = '522421')OR (  ( (Users_3.Nam
e LIKE '%precious%')AND(Groups_1.Type = 'Requestor') ) OR ( 
(CachedGroupMembers_7.GroupId = '266440')AND(Groups_4.Domain = 
'RT::Ticket-Role')AND(Groups_4.Type = 'Requestor') ) OR(main.Cre
ator = '522421') )  ) );

Which lock the db ?? and stop procesing all other queries.
Roy
 
Dirk Pape wrote:

> Hello,
>
> we have very bad performance with searches combining more than two 
> comprisons with OR.
> This is with rt 3.4.5 (as well as 3.6rc3), mysql 4.0.24, 
> DBIx::SearchBuilder 1.43
>
>
> example: Owner='pape' OR Requestor.EmailAddress LIKE 'pape' OR 
> Watcher.EmailAddress LIKE 'pape'
>
> yields to query (mysql-slow.log):
>
> # Query_time: 828  Lock_time: 0  Rows_sent: 1  Rows_examined: 99368997
> SELECT COUNT(DISTINCT main.id) FROM ((((((Tickets main  JOIN Groups 
> Groups_4  ON ( Groups_4.Instance = main.id))  JOIN Groups Groups_1  ON 
> ( Groups_1.Instance = main.id))  LEFT JOIN CachedGroupMembers 
> CachedGroupMembers_5  ON ((CachedGroupMembers_5.GroupId != 
> CachedGroupMembers_5.MemberId)) AND (  CachedGroupMembers_5.GroupId = 
> Groups_4.id))  LEFT JOIN Cache
> dGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.GroupId 
> = Groups_1.id) AND ( (CachedGroupMembers_2.GroupId != 
> CachedGroupMembers_2.MemberId)))  LEFT JOIN Users Users_6  ON ( 
> Users_6.id = CachedGroupMembers_5.MemberId))  LEFT JOIN Users Users_3  
> ON ( Users_3.id = CachedGroupMembers_2.MemberId))   WHERE 
> ((Groups_1.Domain = 'RT::Ticket-Role')) AND
> ((Groups_1.Type = 'Requestor')) AND ((Groups_4.Domain = 
> 'RT::Ticket-Role')) AND ((main.EffectiveId = main.id)) AND 
> ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( 
> (main.Owner = '112') ) AND ( (Users_3.EmailAddress LIKE '%pape%') ) 
> AND ( (Users_6.EmailAddress LIKE '%pape%') ) );
>
> regards,
> Dirk.