alevel_project/storage/sphinx/etc/sphinx-awlex.conf

#
# Minimal Sphinx configuration sample (clean, simple, functional)
#

source awlex-db
{
	type			    = mysql

	sql_host		    = localhost
	sql_user		    = awlex
	sql_pass		    = VskbIskJ
	sql_db			    = awlex
	sql_port		    = 3306	# optional, default is 3306

	sql_query_pre	    = SET NAMES utf8
}

#--------------- more detailed query ---------------------------------------
#---------------------------------------------------------------------------
source src_items : awlex-db
{
	sql_query		    = \
		SELECT \
		    i.id, \
		    i.caption, \
		    i.description, \
		    i.category_id, \
		    cat.title AS category, \
		    i.subcategory_id, \
		    subcat.title AS subcategory, \
		    pic.path AS pictures, \
		    i.created_at \
		FROM items AS i \
        JOIN categories AS cat ON cat.id = i.category_id \
        JOIN subcategories AS subcat ON subcat.id = i.subcategory_id \
        LEFT JOIN \
            (SELECT item_id, GROUP_CONCAT(path SEPARATOR ", ") AS path FROM pictures GROUP BY item_id) as pic \
        ON pic.item_id = i.id \
        GROUP BY i.id;

	sql_field_string	= caption
	sql_field_string	= description
	sql_attr_uint	    = category_id
    sql_field_string	= category
	sql_attr_uint	    = subcategory_id
	sql_field_string	= subcategory
	sql_field_string	= pictures
	sql_attr_timestamp	= created_at

}

index items
{
	source			    = src_items
	path			    = /home/awlex/public_html/awlex_site/storage/sphinx/data/items/items_index
	#morphology          = stem_ru
    morphology          = lemmatize_ru_all, stem_en
	min_word_len        = 3
	min_infix_len       = 2
	ignore_chars        = U+AD
    blend_chars         = +, &, U+23
    blend_mode          = trim_tail, skip_pure
    charset_table       = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+0430..U+044F, U+401->U+0435, U+451->U+0435
}

index items_rt
{
	type			    = rt
	rt_mem_limit	    = 128M
	path			    = /home/awlex/public_html/awlex_site/storage/sphinx/data/items/items_rt
	rt_field		    = caption
	rt_field		    = description
	rt_attr_uint	    = subcategory_id
}

#--------------- less bud not worse ----------------------------------------
#---------------------------------------------------------------------------
source src_items_light : awlex-db
{
    sql_query		    = SELECT * FROM items
	sql_field_string	= caption
	sql_field_string	= description
	sql_attr_uint	    = subcategory_id
}

index items_light : items
{
    source			    = src_items_light
    path			    = /home/awlex/public_html/awlex_site/storage/sphinx/data/items_light/items_light_index
}

index items_light_rt
{
	type			    = rt
	rt_mem_limit	    = 128M
	path			    = /home/awlex/public_html/awlex_site/storage/sphinx/data/items_light/items_light_rt
	rt_field		    = caption
	rt_field		    = description
	rt_attr_uint	    = subcategory_id
}

#---------------------------------------------------------------------------

common {
    lemmatizer_base = /home/awlex/public_html/awlex_site/sphinx-3.0.2/dict
}

indexer
{
	mem_limit		    = 128M
    lemmatizer_cache    = 256M # cache it all
}

searchd
{
	listen			    = 9312
	listen			    = 9306:mysql41
	log			        = /home/awlex/public_html/awlex_site/storage/sphinx/log/searchd.log
	query_log		    = /home/awlex/public_html/awlex_site/storage/sphinx/log/query.log
	read_timeout	    = 5
	max_children	    = 20
	pid_file		    = /home/awlex/public_html/awlex_site/storage/sphinx/log/searchd.pid
	seamless_rotate	    = 1
	preopen_indexes	    = 1
	unlink_old		    = 1
	workers			    = threads # for RT to work
	binlog_path		    = /home/awlex/public_html/awlex_site/storage/sphinx/data/binlog
}