[Rt-devel] Another sql improvement recommendation for rt3.4.0

[Jesse Vincent]

> > Except that would mean storing usernames in all lowercase, as well as
> > all correspondence related to a ticket.
> 
> I didn't mean all columns had to be done this way, just cases where
> it can be done easily.  For example - I see no reason to not store
> user's email addresses in all-lowercase.  Email addresses are naturally
> case-insensitive anyways, so no problems there.  That would remove
> the one slow query I found already.


No, they're not. You may not use a mail system that's case sensitive.
But email address local parts are, at the option of the local system,
case sensitive. From RFC 2821:

	Mailbox = Local-part "@" Domain

     	 Local-part = Dot-string / Quoted-string ; MAY be case-sensitive


> I've also found another one:
> 
> SELECT * FROM Groups WHERE LOWER(Domain) =  'systeminternal' AND LOWER(Type) =  'privileged';
> 
> 500ms (with LOWER()) vs 36 ms (without LOWER()).
> 
> This seems to be an internal query - RT should know the values in its
> own internal tables, no?  Why the case-insensitive search for this?

Likely it needs to be hinted. Open a ticket, ideally with a patch that
adds CASESENITIVE => 1,  to the calls to seachbuilder->Limit.


--