[rt-users] slow content queries

[Kevin Murphy]

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