Bad performance with search

RT Developers
1

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

2

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:

3

Hello Roy,–Am 19. Juni 2006 11:03:29 +0100 schrieb Roy El-Hames rfh@pipex.net:

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 …

no I had no reply.

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