AW: [rt-devel] new experimental searchbuilder

[Warnke, Andreas]

Hello Jesse,

thank you for this new version 0.82_query2. It fixes what
Andreas Warnke und me described as "Missing brackets in LEFT JOIN,
Searchbuilder 0.81". In comparision with 0.81_04, it increases speed
dramatically when searching for some alternatives in a keywordselect.

I did not notice any loss of speed in other queries.

When searching for 3 requestors, using "contains", SearchBuilder
0.82_query2 creates nearly the statement as 0.81_04 did, apart from
some brackets:

SELECT
DISTINCT main.*
FROM (((Tickets main LEFT JOIN Users as Users_6 ON
                                (Watchers_5.Owner = Users_6.id))
                     LEFT JOIN Users as Users_2 ON
                                (Watchers_1.Owner = Users_2.id))
                     LEFT JOIN Users as Users_4 ON
                                (Watchers_3.Owner = Users_4.id)),
     Watchers Watchers_1, Watchers Watchers_3, Watchers Watchers_5
WHERE ((Watchers_1.Email LIKE '%auer%')OR
       (Users_2.EmailAddress LIKE '%auer%')OR
       (Watchers_3.Email LIKE '%winner%')OR
       (Users_4.EmailAddress LIKE '%winner%')OR
       (Watchers_5.Email LIKE '%arnke%')OR
       (Users_6.EmailAddress LIKE '%arnke%'))
      AND ((Watchers_1.Scope = 'Ticket'))
      AND ((Watchers_1.Type = 'Requestor'))
      AND ((Watchers_3.Scope = 'Ticket'))
      AND ((Watchers_3.Type = 'Requestor'))
      AND ((Watchers_5.Scope = 'Ticket'))
      AND ((Watchers_5.Type = 'Requestor'))
      AND ((main.EffectiveId = main.id))
      AND main.id = Watchers_1.Value
      AND main.id = Watchers_3.Value
      AND main.id = Watchers_5.Value
ORDER BY main.id ASC
LIMIT 50

On our system this takes 205 seconds (1650 tickets, PC of about
200 MHz, MySQL 4.01, RT2.0.15). There is no speed gain in comparision
with
0.81_04. But no loss either.

Some "handwork" allows to have only one "LEFT JOIN" which speeds up
things a lot, but I imagine it's very complicated to have it
automatically done.

Regards,
Horst