Bad performance with search

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

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 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