[Rt-devel] bad performance with search

[Dirk Pape]

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.
-- 
Dr. Dirk Pape (eAS - Projektleitung Campus Management)
Freie Universitaet Berlin
Grunewaldstr. 34a, 12165 Berlin
Tel. +49 (30) 838 75143, Fax. +49 (30) 838 54654