Where I work, we have built up a large catalogue of tickets that we want to be able to search quickly through for similar issues, and OTRS's fulltext search seemed at first like the ideal solution. Testing showed it to be very unreliable and unpredictable however, as it seemed to return either thousands of results or nothing at all.
I investigated the queries being run on the database and found that OTRS uses a LIKE statement for its fulltext search. This is very limiting, doesn't give any advanced operators (such as +word, -word, etc), and is slow because it does not use an index.
I came up with a solution that changes this to MySQL's native fulltext-index search, which is described here: http://dev.mysql.com/doc/refman/5.1/en/ ... earch.html
Benefits of this are:
Faster - uses a proper index, so search times are reduced
More Powerful - allows complex expressions and operators
Real-time - no need to run a script to update the index (as with StaticDB), as MySQL does this automatically
1. Add the following lines to your my.cnf file and restart MySQL:
Code: Select all
# Fulltext index perameters
ft_stopword_file = ""
ft_min_word_len=3
Code: Select all
ALTER TABLE article ADD FULLTEXT(a_from, a_to, a_cc, a_subject, a_body);
Code: Select all
mkdir -p /opt/otrs/Custom/Kernel/System/Ticket/ArticleSearchIndex/
cp /opt/otrs/Kernel/System/Ticket/ArticleSearchIndex/RuntimeDB.pm /opt/otrs/Custom/Kernel/System/Ticket/ArticleSearchIndex/
*EDIT (cleaned up code and fixed issue with subject/from/to etc searches)
Open /opt/otrs/Custom/Kernel/System/Ticket/ArticleSearchIndex/RuntimeDB.pm in your favourite text editor. For simplicity, you can just delete the contents of the whole file, and paste in the code below:
Code: Select all
# --
# Kernel/System/Ticket/ArticleSearchIndex/RuntimeDB.pm - article search index backend runtime
# Copyright (C) 2001-2010 xxx, http://otrs.org/
# --
# $Id: RuntimeDB.pm,v 1.12 2010/10/16 09:25:35 bes Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --
package Kernel::System::Ticket::ArticleSearchIndex::RuntimeDB;
use strict;
use warnings;
use vars qw($VERSION);
$VERSION = qw($Revision: 1.12 $) [1];
sub ArticleIndexBuild {
my ( $Self, %Param ) = @_;
# check needed stuff
for (qw(ArticleID UserID)) {
if ( !$Param{$_} ) {
$Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
return;
}
}
return 1;
}
sub ArticleIndexDelete {
my ( $Self, %Param ) = @_;
# check needed stuff
for (qw(ArticleID UserID)) {
if ( !$Param{$_} ) {
$Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
return;
}
}
return 1;
}
sub ArticleIndexDeleteTicket {
my ( $Self, %Param ) = @_;
# check needed stuff
for (qw(TicketID UserID)) {
if ( !$Param{$_} ) {
$Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
return;
}
}
return 1;
}
sub _ArticleIndexQuerySQL {
my ( $Self, %Param ) = @_;
# check needed stuff
for (qw(Data)) {
if ( !$Param{$_} ) {
$Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
return;
}
}
# use also article table if required
my $SQL = '';
my $SQLExt = '';
for (
qw(
From To Cc Subject Body
ArticleCreateTimeOlderMinutes ArticleCreateTimeNewerMinutes
ArticleCreateTimeOlderDate ArticleCreateTimeNewerDate
)
)
{
if ( $Param{Data}->{$_} ) {
$SQL = ', article art ';
$SQLExt = ' AND st.id = art.ticket_id';
last;
}
}
return $SQL, $SQLExt;
}
sub _ArticleIndexQuerySQLExt {
my ( $Self, %Param ) = @_;
# check needed stuff
for (qw(Data)) {
if ( !$Param{$_} ) {
$Self->{LogObject}->Log( Priority => 'error', Message => "Need $_!" );
return;
}
}
my %FieldSQLMapFullText = (
From => 'art.a_from',
To => 'art.a_to',
Cc => 'art.a_cc',
Subject => 'art.a_subject',
Body => 'art.a_body',
);
my $SQLExt = '';
my $FullTextSQL = '';
###############################################################################
# Zero the counter
my $checkcount = 0;
# Loop through the hash values to see how many fields we are searching over
for my $Key ( keys %FieldSQLMapFullText ) {
next if !$Param{Data}->{$Key};
$checkcount += 1;
$FullTextSQL = lc $Self->{DBObject}->Quote( $Param{Data}->{$Key} );
}
# If we are searching for 5 fields or over, then we must be running
# a fulltext search.
if ($checkcount >= 5) {
$FullTextSQL = '\'' . $FullTextSQL . '\'';
$SQLExt = ' AND MATCH(a_from, a_to, a_cc, a_subject, a_body) AGAINST( ' . $FullTextSQL . ' IN BOOLEAN MODE) ';
# Return here. This cuts off any other search terms entered, but this
# is in line with stock behavour!
return $SQLExt;
}
# If we aren't running a fulltext search, remove values and contuine on.
$FullTextSQL = '';
###############################################################################
for my $Key ( keys %FieldSQLMapFullText ) {
next if !$Param{Data}->{$Key};
# replace * by % for SQL like
$Param{Data}->{$Key} =~ s/\*/%/gi;
# check search attribute, we do not need to search for *
next if $Param{Data}->{$Key} =~ /^\%{1,3}$/;
if ($FullTextSQL) {
$FullTextSQL .= ' ' . $Param{Data}->{ContentSearch} . ' ';
}
# check if search condition extension is used
if ( $Param{Data}->{ConditionInline} ) {
$FullTextSQL .= $Self->{DBObject}->QueryCondition(
Key => $FieldSQLMapFullText{$Key},
Value => $Param{Data}->{$Key},
SearchPrefix => $Param{Data}->{ContentSearchPrefix},
SearchSuffix => $Param{Data}->{ContentSearchSuffix},
Extended => 1,
);
}
else {
my $Field = $FieldSQLMapFullText{$Key};
my $Value = $Param{Data}->{$Key};
if ( $Param{Data}->{ContentSearchPrefix} ) {
$Value = $Param{Data}->{ContentSearchPrefix} . $Value;
}
if ( $Param{Data}->{ContentSearchSuffix} ) {
$Value .= $Param{Data}->{ContentSearchSuffix};
}
# replace %% by % for SQL
$Param{Data}->{$Key} =~ s/%%/%/gi;
# db quote
$Value = $Self->{DBObject}->Quote( $Value, 'Like' );
# check if database supports LIKE in large text types (in this case for body)
if ( $Self->{DBObject}->GetDatabaseFunction('CaseInsensitive') ) {
$FullTextSQL .= " $Field LIKE '$Value'";
}
elsif ( $Self->{DBObject}->GetDatabaseFunction('LcaseLikeInLargeText') ) {
$FullTextSQL .= " LCASE($Field) LIKE LCASE('$Value')";
}
else {
$FullTextSQL .= " LOWER($Field) LIKE LOWER('$Value')";
}
}
}
if ($FullTextSQL) {
$SQLExt = ' AND (' . $FullTextSQL . ')';
}
return $SQLExt;
}
1;
5. Re-run permissions script
Run the /opt/otrs/bin/otrs.SetPermissions.pl script with settings appropriate for your install. Examples can be found in section 7 of the INSTALL file in the OTRS directory.
6. Make sure OTRS is set to use RuntimeDB for fulltext search, and NOT StaticDB
Go to Sysconfig and navigate to:
Ticket -> Core::FulltextSearch - Ticket::SearchIndexModule
Make sure it is set to 'RuntimeDB'. If you have set it to StaticDB deliberately (to try to improve fulltext search speed) then it is safe to switch it back to RuntimeDB.
7. Try it out!
I've set the SQL command to use 'IN BOOLEAN MODE', which gives you these operators to use: http://dev.mysql.com/doc/refman/5.1/en/ ... olean.html
Searches with double-quotes will return EXACTLY what you put between them. So if you search for "any" it will only find every instance of the word 'any' on it's own. It will not find 'anyone' 'anywhere' 'toany' etc. If you want these, include them in quotes as well - "any" "anyone" "anywhere"
Same for plurals - "computer" will not find 'computers'. If you want both, put them both in - "computer" "computers", or just search for computer*
I've found this to be far more accurate and useful than the default LIKE search. It would be great to hear from anyone who tries this, and what their own thoughts on it are.