Minor bug in user search

I’ve found a minor bug with the user search function: the ACTION in the form
tag is given as a path value rather than an explicit page, e.g.
Action="/Admin/Users/".

This is a problem for those of us using Apache2, because the DirectoryIndex
directive does not work with RT and we have to set up a redirect using the
RedirectMatch directive (this is mentioned in the "Configuring Apache"
section of draftmanual.pdf).

So what happens is the form data (i.e. our search criteria) are posted to
"/Admin/Users/", but Apache2 redirects this to “/Admin/Users/index.html”.
Because the redirect does not pass through the POST parameters, we arrive
back at /Admin/Users/index.html with no search results as if we had done a
simple GET on that page.

Of course, this will be a problem on any form that POSTs to "path-only"
URL (although I haven’t yet found any other instances of this outside of
user search)

So really, one of two things needs to happen:

  1. We need to figure out how the @#$% to get Apache2 to respect the
    DirectoryIndex directive in RT URL’s
  2. We need to stop using “path-only” URLs, at least when it comes to HTTP
    POSTing.

Being lazy (and somewhat ignorant of Apache administration) I tried #2 on my
local install: I appended “index.html” to the ACTION parameter of the form
tag in /share/html/Admin/User/index.html and now user searching works just
fine. Maybe someone with CVS rights could patch this for the next release?

Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail

Hi all,

(Appologies for the HTML mail, but it made the formatting below much
easier…)

Well, I haven’t seen anything on the list for a little while, so I thought I
would chime in :slight_smile:

When I am searching on multiple custom fields there is a serious performance
problem. The query is show on screen as follows:

Current search criteria
Queue = Client Issues [delete]
CF.Client Issues.{Client Reference} = 94 [delete]
CF.Client Issues.{Client Reference} = 49 [delete]
CF.Client Issues.{Client Reference} = 97 [delete]
CF.Client Issues.{Client Reference} = 110 [delete]
CF.Client Issues.{Client Reference} = 71 [delete]
CF.Client Issues.{Client Reference} = 53 [delete]

The SQL that it generates (with seachbuilder 0.86 and 0.89_2) is:
(formatted to make it easier to read, and some extraneous brackets removed
for the same reason)

SELECT count(main.id)
FROM ((((((Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_6
ON ( main.id = TicketCustomFieldValues_6.Ticket)
AND( (TicketCustomFieldValues_6.CustomField = ‘40’)))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_3
ON ((TicketCustomFieldValues_3.CustomField = ‘40’))
AND( main.id = TicketCustomFieldValues_3.Ticket))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_2
ON ( main.id = TicketCustomFieldValues_2.Ticket)
AND( (TicketCustomFieldValues_2.CustomField = ‘40’)))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_5
ON ((TicketCustomFieldValues_5.CustomField = ‘40’))
AND( main.id = TicketCustomFieldValues_5.Ticket))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_4
ON ( main.id = TicketCustomFieldValues_4.Ticket)
AND( (TicketCustomFieldValues_4.CustomField = ‘40’)))
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1
ON ( main.id = TicketCustomFieldValues_1.Ticket)
AND( (TicketCustomFieldValues_1.CustomField = ‘40’)))
WHERE main.EffectiveId = main.id
AND main.Type = 'ticket’
AND ( ( TicketCustomFieldValues_1.Content = '49’
OR TicketCustomFieldValues_2.Content = '94’
OR TicketCustomFieldValues_3.Content = '97’
OR TicketCustomFieldValues_4.Content = '110’
OR TicketCustomFieldValues_5.Content = '71’
OR TicketCustomFieldValues_6.Content = ‘53’ )
AND main.Queue = ‘5’ );

When this gets thrown against our current (postgres) database, it takes 29
seconds to run. An almost identical query is then executed to actually get
the rows that were just counted. The problem here is clear to see: six left
joins of the same table.

When this is simplified to the following:

SELECT count(main.id)
FROM (Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1
ON ( main.id = TicketCustomFieldValues_6.Ticket)
AND(TicketCustomFieldValues_6.CustomField = ‘40’))
WHERE (main.EffectiveId = main.id)
AND main.Type = 'ticket’
AND ( ( TicketCustomFieldValues_1.Content = '49’
OR TicketCustomFieldValues_2.Content = '94’
OR TicketCustomFieldValues_3.Content = '97’
OR TicketCustomFieldValues_4.Content = '110’
OR TicketCustomFieldValues_5.Content = '71’
OR TicketCustomFieldValues_6.Content = ‘53’ )
AND main.Queue = ‘5’ );

The query goes down to 6.7 seconds. Already a significant improvement. If we
can further simplify this to:

SELECT count(main.id)
FROM (Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1
ON ( main.id = TicketCustomFieldValues_1.Ticket)
AND(TicketCustomFieldValues_1.CustomField = ‘40’))
WHERE (main.EffectiveId = main.id)
AND main.Type = 'ticket’
AND ( TicketCustomFieldValues_1.Content = '49’
OR TicketCustomFieldValues_1.Content = '94’
OR TicketCustomFieldValues_1.Content = '97’
OR TicketCustomFieldValues_1.Content = '110’
OR TicketCustomFieldValues_1.Content = '71’
OR TicketCustomFieldValues_1.Content = ‘53’ )
AND main.Queue = ‘5’;

The execution time goes down to 4.8 seconds. Better still. However, if we
can simplify this to the following:

SELECT count(main.id)
FROM Tickets main,
TicketCustomFieldValues as TicketCustomFieldValues_1
WHERE main.id = TicketCustomFieldValues_1.Ticket
AND TicketCustomFieldValues_1.CustomField = '40’
AND main.EffectiveId = main.id
AND main.Type = 'ticket’
AND ( TicketCustomFieldValues_1.Content = '49’
OR TicketCustomFieldValues_1.Content = '94’
OR TicketCustomFieldValues_1.Content = '97’
OR TicketCustomFieldValues_1.Content = '110’
OR TicketCustomFieldValues_1.Content = '71’
OR TicketCustomFieldValues_1.Content = ‘53’ )
AND main.Queue = ‘5’;

Thebn we can get the excution times down to 6.4 milliseconds. This is
roughly 5000 times faster than the original query.

That’s the good news! :slight_smile: The bad news is that I don’t know how to modify
SearchBuilder to produce this new optimised SQL.

But, I do hope that this analysis can help someone else :slight_smile:

If you want any further information, please mail me,

Cheers,
Paul

Hi Jesse,

The patch that I got from Robrt was actually for something different… The
patch that I got was for issue 2490 (Problem with custom field searching in
RT 3.0.2pre4). Although issue 3200 seems to supersede this (perhaps a link
between the two can be added?)

However, you got me to thinking, and so I went searching for the work that
Robrt had done, and came across issue 3201 (Use less joins for Custom
Fields) which you have already applied as change 229.

This does fix the problem! :slight_smile: However please continue reading the e-mail!
:slight_smile:

This also means that Issue 3287 should be (at least) linked to 3201, and
possibly marked as resolved as well.

However, one (minor) point is that the SQL that does get generated looks as
follows:

SELECT count(main.id)
FROM ((((((Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1
ON ( main.id = TicketCustomFieldValues_1.Ticket)
AND( (TicketCustomFieldValues_1.CustomField = ‘40’)
OR (TicketCustomFieldValues_1.CustomField = ‘40’)
OR (TicketCustomFieldValues_1.CustomField = ‘40’)
OR (TicketCustomFieldValues_1.CustomField = ‘40’)
OR (TicketCustomFieldValues_1.CustomField = ‘40’)
OR (TicketCustomFieldValues_1.CustomField = ‘40’)
OR (TicketCustomFieldValues_1.CustomField = ‘40’)))
WHERE main.EffectiveId = main.id
AND main.Type = 'ticket’
AND ( ( TicketCustomFieldValues_1.Content = '49’
OR TicketCustomFieldValues_1.Content = '94’
OR TicketCustomFieldValues_1.Content = '97’
OR TicketCustomFieldValues_1.Content = '110’
OR TicketCustomFieldValues_1.Content = '71’
OR TicketCustomFieldValues_1.Content = ‘53’ )
AND main.Queue = ‘5’ );

This leads to only a very minor inefficiency as the postgres query optimiser
just removes the duplicates, but still it looks strange :slight_smile:

However, as mentioned in #3287, at least for postgres (I don’t use mysql so
I cannot speak for it) if you can replace the left join with an inner join,
i.e. just by replacing the word ‘LEFT’ above with the word ‘INNER’. In this
particular case we know that this is safe since both sides have to exist? I
have done that locally, and the difference is noticeable. I did this by
applying Robrt’s patch and then changing line 729 to say ‘inner’ instead of
’left’. As mentioned before, I do not know what difference this will have on
MySQL, but on postgres, the difference is dramatic (the query is roughly
2500 times faster, 7ms instead of 1700ms)

I had a look at how to remove the repeated
(TicketCustomFieldValues_1.CustomField = ‘40’) tests, and have also attached
a patch for that (I have also attached this patch to issue 3201).

Anyway, once again Jesse thanks for a great product, and for being
responsive to the various issues that we have thrown up.

Cheers,
Paul

CF_orlimit_inner.patch (1.04 KB)