[rt-devel] extremely slow query in rt 3.0.10 due to ILIKE instead of =

[Palle Girgensohn]

--On tisdag, mars 30, 2004 15.47.56 -0500 Jesse Vincent 
<jesse at bestpractical.com> wrote:

>> I'll ask the postgresql developers about this. We'll see what they
>> say. I also found the lines in SearchBuilder that handle this. Changed
>> between 0.98_1 and 0.99... So, I can downgrade for now, I guess...
>> Still, do you really need to match ID:s case-insensitive?
>
> It's how searchbuilder-based apps work on every other database platform.
> Changing that behavior or making it db-specific worries me, especially
> since a lot of the data is stuff that humans search on.

hmm, ok, but these are not search criterias, they are join conditions. They 
are ID:s, internal integers that are not shown to anyone or used in the 
GUI, right? Integers should not need to be case-insensitive? And they 
compare two db fields, not field with value.

These are the fields that are ILIKEd in this query. They're all integers:

Users.id
CachedGroupMembers.MemberId
CachedGroupMembers.GroupId
Tickets.id
Groups.Instance

SearchBuilder seem to separate join conditions from search restrictions. I 
tried returning "lower($field)" , ... , "lower($value)" in 
Handle/Pg.pm:_MakeClauseCaseInsensitive(), but the single quotes are added 
later down the code, so it became 'lower(value)' instead of lower('value'). 
Where are the single quotes added to the query string?

/Palle