Fulltext search for Tiny Tiny RSS (TTRSS) with Sphinx and MySQL in Debian/Ubuntu

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.