I haven’t found a working tutorial on setting up Sphinx fulltext search for the awesome Tiny Tiny RSS Reader (TTRSS) and MySQL in Debian/Ubuntu. So, without further ado, here it is:
apt-get install sphinxsearch
Create /etc/sphinxsearch/sphinx.conf:
source ttrss { type = mysql sql_host = localhost sql_user = ttrss sql_pass = changeme sql_db = ttrss sql_port = 3306 sql_query_pre = SET NAMES utf8 # UNIX socket name # optional, default is empty (reuse client library defaults) # usually '/var/lib/mysql/mysql.sock' on Linux # usually '/tmp/mysql.sock' on FreeBSD # # sql_sock = /var/lib/mysql/mysql.sock sql_query = \ SELECT int_id AS id, ref_id, UNIX_TIMESTAMP() AS updated, \ ttrss_entries.title AS title, link, content, \ ttrss_feeds.title AS feed_title, \ marked, published, unread, \ author, ttrss_user_entries.owner_uid \ FROM ttrss_entries, ttrss_user_entries, ttrss_feeds \ WHERE ref_id = ttrss_entries.id AND feed_id = ttrss_feeds.id; sql_attr_uint = owner_uid sql_attr_uint = ref_id sql_ranged_throttle = 0 sql_query_info = \ SELECT * FROM ttrss_entries, \ ttrss_user_entries WHERE ref_id = id AND int_id=$id } source delta : ttrss { sql_query = \ SELECT int_id AS id, ref_id, UNIX_TIMESTAMP() AS updated, \ ttrss_entries.title AS title, link, content, \ ttrss_feeds.title AS feed_title, \ marked, published, unread, \ author, ttrss_user_entries.owner_uid \ FROM ttrss_entries, ttrss_user_entries, ttrss_feeds \ WHERE ref_id = ttrss_entries.id AND feed_id = ttrss_feeds.id \ AND ttrss_entries.updated > UNIX_TIMESTAMP() - INTERVAL 24 HOUR; sql_query_killlist = \ SELECT int_id FROM ttrss_entries, ttrss_user_entries \ WHERE ref_id = ttrss_entries.id AND updated > UNIX_TIMESTAMP() - INTERVAL 24 HOUR; } index ttrss { source = ttrss path = /var/lib/sphinxsearch/data/ttrss docinfo = extern mlock = 0 morphology = none min_word_len = 1 charset_type = utf-8 min_prefix_len = 3 prefix_fields = title, content, feed_title, author enable_star = 1 html_strip = 1 } index delta : ttrss { source = delta path = /var/lib/sphinxsearch/data/ttrss_delta } indexer { mem_limit = 32M } searchd { listen = 127.0.0.1:9312 log = /var/log/sphinxsearch/searchd.log query_log = /var/log/sphinxsearch/query.log read_timeout = 5 client_timeout = 300 max_children = 30 pid_file = /var/run/sphinxsearch/searchd.pid max_matches = 1000 seamless_rotate = 1 preopen_indexes = 1 unlink_old = 1 mva_updates_pool = 1M max_packet_size = 8M max_filters = 256 max_filter_values = 4096 compat_sphinxql_magics = 0 }
Create the indices using indexer --all
Set START=yes
in /etc/default/sphinxsearch and start Sphinx using service sphinxsearch start
The last step is to enable Sphinx search in TTRSS:
// ********************* // *** Sphinx search *** // ********************* define('SPHINX_ENABLED', true); // Enable fulltext search using Sphinx (http://www.sphinxsearch.com) // Please see http://tt-rss.org/wiki/SphinxSearch for more information
And that is it. Lighting-fast full-text search in TTRSS! Probably only useful if you have a lot of feeds/articles and you’re keeping them for quite while before purging them to oblivion.
Thank you for this article. Managed to setup SphinxSearch for TT-RSS using PostgreSQL. Just need those last commands to get it to index properly.