User:Hef/rt search

Log into mariadb as root and run INSTALL SONAME 'ha_sphinx'; then show engines; to confirm it worked

Create Table

CREATE TABLE AttachmentsIndex
(
    id          BIGINT NOT NULL,
    weight      INTEGER NOT NULL,
    query       VARCHAR(3072) NOT NULL,
    group_id    INTEGER,
    INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/rt" CHARACTER SET utf8;

More output

You can now configure RT to use the newly-created full-text index by
adding the following to your RT_SiteConfig.pm:

Set( %FullTextSearch,
    Enable     => 1,
    Indexed    => 1,
    MaxMatches => '10000',
    Table      => 'AttachmentsIndex',
);

Below is a simple Sphinx configuration which can be used to index all
text/plain attachments in your database.  This configuration is not
ideal; you should read the Sphinx documentation to understand how to
configure it to better suit your needs.

source rt {
    type            = mysql

    sql_host        = localhost
    sql_db          = rt4
    sql_user        = rt_user
    sql_pass        = rt_pass

    sql_query_pre   = SET NAMES utf8
    sql_query       = \
        SELECT a.id, a.content FROM Attachments a \
        JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \
        JOIN Tickets t ON txn.ObjectId = t.id \
        WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted'

    sql_query_info  = SELECT * FROM Attachments WHERE id=$id
}

index rt {
    source                  = rt
    path                    = /opt/rt4/var/sphinx/index
    docinfo                 = extern
    charset_type            = utf-8
}

indexer {
    mem_limit               = 32M
}

searchd {
    port                    = 9312
    log                     = /opt/rt4/var/sphinx/searchd.log
    query_log               = /opt/rt4/var/sphinx/query.log
    read_timeout            = 5
    max_children            = 30
    pid_file                = /opt/rt4/var/sphinx/searchd.pid
    max_matches             = 10000
    seamless_rotate         = 1
    preopen_indexes         = 0
    unlink_old              = 1
}

Prime things

   sudo groupadd sphinx
   sudo useradd -g sphinx -d /usr/share/sphinx/ -s /bin/false sphinx
   sudo chown -R sphinx:sphinx /var/lib/sphinx/
   sudo -u sphinx indexer --all
   sudo systemctl start sphinx