Sphinx for Rotation\Tube sites

There is a good tool to search within big DBs - Sphinx. This a system to perform a fast search with morphological analysis of search query.

Installation:

• Ask admin to install sphinx itself (this is a separate application)
• create folder scj/sphinx
• Add SmartCJ index to Sphinx (usually you have to ask admin to do it). Config example

source scj
{
	type					= mysql

	sql_host				= localhost
	sql_user				= scj_mysql_login
	sql_pass				= scj_mysql_password
	sql_db					= scj_db
	sql_port				= 3306	# optional, default is 3306

	sql_query				= SELECT id, crc32(g.gallery_md5) as gallery_md5_crc, UNIX_TIMESTAMP(activation_date) as date, tags, alt, description, duration, sponsor_id, rgroup, content_type, gs.total_ctr FROM rot_galleries as g \
							  JOIN rot_gallery_stats AS gs ON g.id = gs.thumb_id \
							  JOIN rot_gallery_info AS gi ON g.gallery_md5 = gi.gallery_md5 \
							  JOIN rot_gallery_data AS gd ON g.gallery_md5 = gd.gallery_md5 \
							  WHERE status = 1 and gs.best_thumb = 'yes' and rgroup != 0
	sql_attr_timestamp		= date
	sql_attr_uint		    = duration
	sql_attr_uint		    = sponsor_id
	sql_attr_uint		    = rgroup
	sql_attr_float		    = total_ctr
	sql_attr_uint		    = content_type
	sql_attr_uint			= gallery_md5_crc

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


index scj_index
{
	source					= scj
	path					= /path_to_data/scj/sphinx/scj_index
	docinfo					= extern
	morphology                              = stem_en # or stem_ru for example
}



You have to replace with your data following values

scj_mysql_login
scj_mysql_password
scj_db
path_to_data

• Ask admin to run DB indexer

   indexer --all --rotate 

• Add to scj/includes/config.php following lines

  $config["sphinx_port"] = 3312; 
  $config["sphinx_search_index"] = 'scj_index';

That's all.