[rt-users] slow content queries

[Ruslan Zakirov]

Kevin, if you create patch that makes this block optional (with RT
config option) then I think it could be applied to the mainline,
because this is behaviour that the most users want.

On 12/7/05, Kevin Murphy <murphy at genome.chop.edu> wrote:
> Stephen Turner wrote:
>
> > I haven't played with this, but if you look in Tickets_Overlay.pm
> > there's a method called _TransLimit which seems to form the SQL
> > clauses necessary for searching on transaction content. This would
> > probably be the place to add the 'filename is null' condition so that
> > it would be in effect for all ticket searches.
> >
> Thanks, Steve.  In lib/RT/Tickets_Overlay.pm, in function _TransLimit, I
> replaced:
>
>        $self->_SQLLimit(
>                         ALIAS         => $self->{_sql_trattachalias},
>                         FIELD         => $field,
>                         OPERATOR      => $op,
>                         VALUE         => $value,
>                         CASESENSITIVE => 0,
>                         ENTRYAGGREGATOR => 'AND',
>                         @rest
>                        );
>
> with this:
>
>     if ($field eq 'Content') {
>        $self->_SQLLimit(
>                         ALIAS         => $self->{_sql_trattachalias},
>                         FIELD         => 'Filename',
>                         OPERATOR      => 'IS',
>                         VALUE         => 'NULL',
>                         SUBCLAUSE     => 'contentquery',
>                         ENTRYAGGREGATOR => 'AND',
>                        );
>        $self->_SQLLimit(
>                         ALIAS         => $self->{_sql_trattachalias},
>                         FIELD         => $field,
>                         OPERATOR      => $op,
>                         VALUE         => $value,
>                         CASESENSITIVE => 0,
>                         @rest,
>                         ENTRYAGGREGATOR => 'AND',
>                         SUBCLAUSE     => 'contentquery',
>                        );
>     } else {
>        $self->_SQLLimit(
>                         ALIAS         => $self->{_sql_trattachalias},
>                         FIELD         => $field,
>                         OPERATOR      => $op,
>                         VALUE         => $value,
>                         CASESENSITIVE => 0,
>                         ENTRYAGGREGATOR => 'AND',
>                         @rest
>                        );
>     }
>
> This change makes my content queries much faster, because it skips the
> enormous binary (and text, for that matter) file attachments when
> performing the ILIKE on Attachments.Content.  Note that I have little
> idea of what I'm doing (the DBIx::SearchBuilder documentation is not
> exactly complete), but the generated SQL seems OK.  I guess I should
> really put these changes in local/lib/RT/Tickets_Overlay.pm?
>
> -Kevin Murphy
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>
> Download a free sample chapter of RT Essentials from O'Reilly Media at http://rtbook.bestpractical.com
>
> WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
> San Francisco - Find out more at http://bestpractical.com/services/training.html
>


--
Best regards, Ruslan.