Missing brackets in LEFT JOIN, Searchbuilder 0.81

RT Developers
1

Hello Jesse,

we are using your RequestTracker, version 2, together with version
0.81 of DBIx::SearchBuilder and a MySQL 4.01. We heavily - at least
heavier than it seems to have ever been expected - use keywords.
Among other things, we search for
different keywords of the same keywordselect. A resulting SQL
statement (gained from logging the mysqld) is this:
SELECT
DISTINCT
main.*
FROM Tickets main
LEFT JOIN ObjectKeywords as ObjectKeywords_1 ON
(ObjectKeywords_1.KeywordSelect = ‘25’)OR(ObjectKeywords_1.KeywordSelect =
‘25’) AND main.id = ObjectKeywords_1.ObjectId
WHERE ((ObjectKeywords_1.Keyword = ‘817’)OR(ObjectKeywords_1.Keyword =
‘508’)) AND ((ObjectKeywords_1.ObjectType =
‘Ticket’)AND(ObjectKeywords_1.ObjectType = ‘Ticket’)) AND ((main.EffectiveId
= main.id))
ORDER BY main.id ASC LIMIT 50

Please note that there are no extra brackets around the OR-terms
after “LEFT JOIN xyz ON”. This makes the select time intensive
(executed via MyPHPAdmin: 6 minutes on a slow PC, 1300 tickets). With
brackets it’s so fast that I cannot measure the time.

I assume a little change in Searchbuilder.pm would cause a miracle.
But I’m not the wizard … :wink:

Regards,
Horst Gwinner

Horst Gwinner, Mobile Applications
3SOFT GmbH, Frauenweiherstraße 14, 91058 Erlangen, Germany
Telefon: +49/9131/7701-185 mailto:Gwinner@3SOFT.de
Telefax: +49/9131/7701-333 http://www.3SOFT.de

2

Very interesting.

t’m in a netcafe with a funny keyboard, but I’ve attempted a patch.
Can you tell me how
http://bestpractical.com/~jesse/DBIx-SearchBuilder-0.81_leftjoin_1.tar.gz

Does for you?On Thu, May 08, 2003 at 06:47:18PM +0200, Warnke, Andreas wrote:

Hello Jesse,

we are using your RequestTracker, version 2, together with version
0.81 of DBIx::SearchBuilder and a MySQL 4.01. We heavily - at least
heavier than it seems to have ever been expected - use keywords.
Among other things, we search for
different keywords of the same keywordselect. A resulting SQL
statement (gained from logging the mysqld) is this:
SELECT
DISTINCT
main.*
FROM Tickets main
LEFT JOIN ObjectKeywords as ObjectKeywords_1 ON
(ObjectKeywords_1.KeywordSelect = ‘25’)OR(ObjectKeywords_1.KeywordSelect =
‘25’) AND main.id = ObjectKeywords_1.ObjectId
WHERE ((ObjectKeywords_1.Keyword = ‘817’)OR(ObjectKeywords_1.Keyword =
‘508’)) AND ((ObjectKeywords_1.ObjectType =
‘Ticket’)AND(ObjectKeywords_1.ObjectType = ‘Ticket’)) AND ((main.EffectiveId
= main.id))
ORDER BY main.id ASC LIMIT 50

Please note that there are no extra brackets around the OR-terms
after “LEFT JOIN xyz ON”. This makes the select time intensive
(executed via MyPHPAdmin: 6 minutes on a slow PC, 1300 tickets). With
brackets it’s so fast that I cannot measure the time.

I assume a little change in Searchbuilder.pm would cause a miracle.
But I’m not the wizard … :wink:

Regards,
Horst Gwinner

–
Horst Gwinner, Mobile Applications
3SOFT GmbH, Frauenweiherstra�e 14, 91058 Erlangen, Germany
Telefon: +49/9131/7701-185 mailto:Gwinner@3SOFT.de
Telefax: +49/9131/7701-333 http://www.3SOFT.de

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.