Difference between revisions of "User:Hef/rt search"
< User:Hef
Jump to navigation
Jump to search
(Created page with "<pre> Going to run the following in the DB: CREATE TABLE AttachmentsIndex ( id BIGINT NOT NULL, weight INTEGER NOT NULL, query VARCHAR(3072)...") |
|||
(12 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | < | + | Log into mariadb as root and run <code>INSTALL SONAME 'ha_sphinx';</code> then <code>show engines;</code> to confirm it worked |
− | + | == Create Table == | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
+ | <pre> | ||
+ | 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; | ||
+ | </pre> | ||
+ | == More output == | ||
− | + | I added The following to RT_SiteConfig.pm | |
− | |||
+ | <pre> | ||
Set( %FullTextSearch, | Set( %FullTextSearch, | ||
Enable => 1, | Enable => 1, | ||
Line 20: | Line 25: | ||
Table => 'AttachmentsIndex', | Table => 'AttachmentsIndex', | ||
); | ); | ||
+ | </pre> | ||
Below is a simple Sphinx configuration which can be used to index all | Below is a simple Sphinx configuration which can be used to index all | ||
Line 25: | Line 31: | ||
ideal; you should read the Sphinx documentation to understand how to | ideal; you should read the Sphinx documentation to understand how to | ||
configure it to better suit your needs. | configure it to better suit your needs. | ||
+ | <pre> | ||
source rt { | source rt { | ||
Line 56: | Line 63: | ||
searchd { | searchd { | ||
− | port = | + | port = 9312 |
log = /opt/rt4/var/sphinx/searchd.log | log = /opt/rt4/var/sphinx/searchd.log | ||
query_log = /opt/rt4/var/sphinx/query.log | query_log = /opt/rt4/var/sphinx/query.log | ||
Line 67: | Line 74: | ||
unlink_old = 1 | unlink_old = 1 | ||
} | } | ||
+ | |||
</pre> | </pre> | ||
+ | |||
+ | == 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 |
Latest revision as of 04:39, 27 August 2014
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
I added The following to 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