Differences

This shows you the differences between two versions of the page.

--- new_rotation_sphinx [2017/04/07 16:24] – [1.51] admin
+++ new_rotation_sphinx [2026/03/21 10:11] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== Sphinx for Rotation\Tube sites ======
-Basically script uses mysql to search DB, but on large DB mysql too slow. + it doesnt use morphology.
+When your database exceeds 100,000 galleries, standard MySQL search becomes slow.
-Sphinx (sphinxsearch.com) - is an open source full text search server, designed from the ground up with performance, relevance (aka search quality), and integration simplicity in mind. It faster then Mysql and deliver better quality of results.
+Sphinx is a fast search engine with morphology support. It's worth implementing even before your database reaches that size due to the benefits of morphological searching.
+Sphinx extracts keywords from your database, creating an optimized search index. The process works like this: your script queries Sphinx, which returns gallery IDs, and then the script retrieves full data from MySQL. This means that when your MySQL database changes, the Sphinx index needs updating too.
-Setup
+Sphinx creates a search index by analyzing your entire database, enabling fast and accurate searching. However, this index must be periodically refreshed. Database changes only appear in the Sphinx index after reindexing.
-  * Ask admin to setup sphinx
+Full reindexing of large databases is resource-intensive, so Sphinx offers Delta Indexing. This allows you to index only new records quickly, then periodically merge the delta index with the main index.
-  * create folder scj/sphinx
-  * ask admin to add scj index into sphinx config. (See config example below )
-  * Run indexer (or ask admin to do it) <code> indexer --all --rotate </code>
-  * In Rotation - Settings edit sphinx options.
-That's it.
+You can also index your search query log with Sphinx to find similar searches (Sphinx Search Log Index).
-**Note** if you change DB (add\remove content) - please, run indexer every day to keep it's base up to date.
+Sphinx offers different search modes (Sphinx Search Mod) and result ranking options (Sphinx Ranker Mod). See the Sphinx documentation for details.
-Add to cron something like (ask admin)
+Common search terms can return huge result sets with excessive pagination. Sphinx Max Matches lets you limit the maximum number of results returned.
+===== Sphinx installation =====
+  * Ask your admin to install Sphinx
+  * Create the directory scj/sphinx
+  * Add the index configuration to Sphinx config (usually done by admin). Find the configuration in Rotation Settings - Site Search
+  * Run database indexing (usually done by admin): `indexer --all --rotate`
+  * You only need to configure the settings in Rotation Settings, which you can get from your admin
+That's it.
+You need to periodically run database indexing:
 <code>
 indexer --all --rotate
@@ Line 25: / Line 36: @@
-====== Sphinx configs TCMS  ======
+===== Sphinx Delta Config =====
-===== TCMS (version 2.X) =====
+The Sphinx index captures your database at a point in time. Any changes made after indexing won't be discoverable by Sphinx.
-<code>
+This means you'd need to reindex after every database change, but full reindexing is time-consuming and resource-intensive on large databases.
+Delta indexing solves this. A delta index captures changes made since the last full index, and since it contains only new data, it builds very quickly.
-searchd
+Sphinx searches both the main index and delta index together, showing all changes. You can create delta indexes as frequently as needed.
-{
-	listen      			= your_port:mysql41
-addition of mysql41 is the most important part. It's called SphinxQL
+Result:
+in the main part which you already have you need to add after
-source your_name_source
+  sql_query_pre = SET NAMES utf8
-{
-	type					= mysql
-	sql_host				= localhost
+line
-	sql_user				=
-	sql_pass				=
-	sql_db					=
-	sql_port				= 3306	# optional, default is 3306
-	sql_query_pre = SET NAMES utf8
+  sql_query_pre = UPDATE rot_settings SET value = (SELECT MAX(gallery_id) FROM rot_gallery_info) WHERE name = 'sphinx_max_gallery_id'
-	sql_query				= SELECT gi.gallery_id, UNIX_TIMESTAMP(gi.activation_date) as date, alt, description, gi.duration, sponsor_id, gs.total_ctr, gi.content_type, \
-							(SELECT group_concat(tag_name) FROM rot_gal2tag g2t \
-							LEFT JOIN rot_tags as t on t.tag_id = g2t.tag_id \
-							WHERE g2t.gallery_id = gi.gallery_id) as tags, \
-							(SELECT group_concat(tag_id) FROM rot_gal2tag g2t \
-							WHERE g2t.gallery_id = gi.gallery_id) as tag_ids, \
-							(SELECT group_concat(gss.group_id) FROM rot_gallery_stats1 as gss \
-							WHERE gss.gallery_id = gi.gallery_id AND group_id != 0) as categories \
-							FROM rot_gallery_info AS gi \
-							JOIN rot_gallery_data1 AS gd ON gi.gallery_id = gd.gallery_id \
-							JOIN rot_gallery_stats1 AS gs ON gs.gallery_id = gi.gallery_id \
-							WHERE gallery_status = 'active' and gallery_type = 0 \
-							and gs.best_thumb = 'yes' and gs.group_id = 0
+Now you need to add the creation of a delta index
-	sql_attr_timestamp		= date
+<code>
-	sql_attr_uint		    = duration
-	sql_attr_uint		    = sponsor_id
-	sql_attr_float		    = total_ctr
-	sql_attr_uint		    = content_type
-    sql_attr_multi 			= uint categories from field;
-    sql_attr_multi 			= uint tag_ids from field;
+source delta : your_name_source
+{
+    sql_query_pre = SET NAMES utf8
+	sql_query				= SELECT gi.gallery_id, UNIX_TIMESTAMP(gi.activation_date) as date, alt, description, gi.duration, sponsor_id, gs.total_ctr, gi.content_type, 							(SELECT group_concat(tag_name) FROM rot_gal2tag g2t 							LEFT JOIN rot_tags as t on t.tag_id = g2t.tag_id 							WHERE g2t.gallery_id = gi.gallery_id) as tags, 							(SELECT group_concat(tag_id) FROM rot_gal2tag g2t 							WHERE g2t.gallery_id = gi.gallery_id) as tag_ids,
+							(SELECT group_concat(name) FROM rot_groups  							WHERE rot_groups.id in (SELECT group_id FROM rot_gallery_stats1 WHERE rot_gallery_stats1.gallery_id = gi.gallery_id AND group_id != 0) ) as group_names,
+							(SELECT group_concat(gss.group_id) FROM rot_gallery_stats1 as gss 							WHERE gss.gallery_id = gi.gallery_id AND group_id != 0) as categories 							FROM rot_gallery_info AS gi 							JOIN rot_gallery_data1 AS gd ON gi.gallery_id = gd.gallery_id 							JOIN rot_gallery_stats1 AS gs ON gs.gallery_id = gi.gallery_id 							WHERE gi.gallery_id > ( SELECT value FROM rot_settings WHERE name = 'sphinx_max_gallery_id' ) 							AND gallery_status = 'active' and gallery_type = 0 							and gs.best_thumb = 'yes' and gs.group_id = 0
 }
-index your_name_index
+index delta : your_name_index
 {
-	source					= your_name_source
+    source = delta
-	path					= /your_path
+    path = /your_full_path/data/delta
-	docinfo					= extern
-	morphology              = stem_en
-	charset_type 			= utf-8
 }
 </code>
-===== 1.52 =====
+As you can see the query is the same, the only difference is the presence of sphinx_max_gallery_id in the selection.
+After that you need to add indexing of new records to the cron as often as you like
+  indexer --rotate delta
+After creating the index you need to specify delta in the Sphinx Delta Index settings of rotation.
+and once a day for example you can attach the delta index to the main one
+  indexer --rotate --merge your_name_index delta
+All.
+===== Search Log =====
+When users search on your site (domain.com/?search=...), queries are logged to the database. Over time, you accumulate many searches. Indexing these queries with Sphinx lets you find similar searches using morphology.
+To do this add to the Sphinx config
 <code>
-source scj
+source search_queries
 {
 	type					= mysql
-	sql_host				= localhost
+	sql_host				= YOUR_HOST
-	sql_user				= scj_mysql_login
+	sql_user				= USERNAME
-	sql_pass				= scj_mysql_password
+	sql_pass				= PASSWORD
-	sql_db					= scj_db
+	sql_db					= DB_NAME
 	sql_port				= 3306	# optional, default is 3306
-	sql_query				= SELECT id, date, tags, alt, description, duration, sponsor_id, rgroup FROM rot_galleries as g \
+    sql_query_pre = SET NAMES utf8
-							  JOIN rot_gallery_stats AS gs ON g.id = gs.thumb_id WHERE status = 1 and gs.best_thumb = 'yes' and rgroup != 0
-        // for version 49
-	//sql_query				= SELECT id, UNIX_TIMESTAMP(activation_date) as date, tags, alt, description, duration, sponsor_id, rgroup FROM rot_galleries as g \
-	//						  JOIN rot_gallery_stats AS gs ON g.id = gs.thumb_id JOIN rot_gallery_data AS gd ON gd.gallery_md5 = g.gallery_md5 WHERE status = 1 and gs.best_thumb = 'yes' and rgroup != 0
-	sql_attr_timestamp		= date
+	sql_query				= SELECT sq_id, search_query, hits, items_found FROM rot_search_queries WHERE hits > 0  GROUP BY search_query
-	sql_attr_uint		    = duration
-	sql_attr_uint		    = sponsor_id
+	sql_attr_uint		    = hits
-	sql_attr_uint		    = rgroup
+	sql_attr_uint		    = items_found
-	sql_query_info			        = SELECT * FROM rot_galleries WHERE id=$id
 }
-index scj_index
+index search_queries_index
 {
-	source					= scj
+	source					= search_queries
-	path					= /path_to_data/scj/sphinx/scj_index
+	path					= /WHERE_TO_STORE
 	docinfo					= extern
-	morphology                              = stem_en # or stem_ru for example
+	morphology              = stem_en
 }
 </code>
-===== 1.51 =====
-<code>
-source scj
-{
-	type					= mysql
-	sql_host				=  ............
+Specify this in Rotation Settings - Sphinx Search Log Index. The script will use this index to find similar queries for display. For example, when searching for a keyword, you can show a list of similar searches using `<thumb search_log=all filter=GET_search>`, which leverages Sphinx's speed and morphology.
-	sql_user				=  .............
-	sql_pass				=  ..............
-	sql_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, gs.total_ctr, \
+Don't forget to reindex regularly. For example: you search for "vasya" and get 10 results, with gallery ID 123 being the best (stored as record ID 789 in the search log—note these are different IDs). Sphinx returns ID 789. Later, the search returns a different best result stored as ID 888. If you haven't reindexed Sphinx, it still returns the old ID 789, which no longer exists or now contains different content.
-							  g.content_type, (SELECT group_concat(group_id) FROM rot_gal2group as g2gr WHERE g2gr.gal_id = g.id) as categories FROM rot_galleries as g JOIN rot_gallery_stats AS gs ON g.id = gs.thumb_id \
+===== Sphinx settings =====
-							  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 and gs.group_id = 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_attr_multi 			= uint categories from field;
-	sql_query_info			= SELECT * FROM rot_galleries WHERE id=$id
+Sphinx Search Index main index
-}
+Sphinx Delta Index - delta index
+Sphinx Search Log Index
+Sphinx Search Mod
+for advanced user only, do not change if you are not sure
+Sphinx Ranker Mod
+for advanced user only, do not change if you are not sure
+Sphinx Max Matches
+for advanced user only, do not change if you are not sure
+default 20000
-You have to edit here:
-scj_mysql_login
+===== Sphinx for child (slave) sites =====
-scj_mysql_password
-scj_db
-path_to_data
-</code>
-===== Sphinx Delta Config =====
-For Version 2.X
+Slaves use Sphinx data from the master's database by default. If Sphinx is set up on the master, all slaves automatically use it.
-This part make sense only if you have a really big DB (1M+ , for example a demo version bigbase.smartcj.com has 12M+ galleries)
+One issue to watch for: Sphinx host is typically set to localhost. If a slave is on a different server, localhost won't work. Instead, specify the actual IP address of the Sphinx server. Also verify that connections to this port are allowed from the slave server to the Sphinx server.
-When Sphinx creates an index it takes a snapshot of a current state of a DB. So if something has been changed since then - it won't be reflected in index. And if you have a big DB it takes a lot of time to reindex it.
-So the good idea is to just reindex those parts that have been changed. That's why you need so called delta index
+===== Sphinx Search CTR  =====
-To make use of it you need to add after
+Sphinx search defaults to relevance sorting (domain/?search=...). You can also sort by date (domain/?search=...&order=date) or duration (domain/?search=...&order=duration).
-  sql_query_pre = SET NAMES utf8
-a new line
+===== Sphinx settings =====
-  sql_query_pre = UPDATE rot_settings SET value = (SELECT MAX(gallery_id) FROM rot_gallery_info) WHERE name = 'sphinx_max_gallery_id'
+Sphinx has great capabilities for tuning indexing and search. For example, it's a good idea to add these optional lines to the config.
+  min_word_len = 3 # Minimum length of indexed word
+  min_infix_len = 2 # Minimum infix length (prefix included)
+  enable_star = 1 # Use truncation operator "*"
-And add a new source
-<code>
+But of course it's best to go through the Sphinx documentation and choose the parameters for yourself.
-source delta : your_name_source
-{
-    sql_query_pre = SET NAMES utf8
-	sql_query				= SELECT gi.gallery_id, UNIX_TIMESTAMP(gi.activation_date) as date, alt, description, gi.duration, sponsor_id, gs.total_ctr, gi.content_type, \
+===== Sphinx Reindex =====
-							(SELECT group_concat(tag_name) FROM rot_gal2tag g2t \
-							LEFT JOIN rot_tags as t on t.tag_id = g2t.tag_id \
-							WHERE g2t.gallery_id = gi.gallery_id) as tags, \
-							(SELECT group_concat(gss.group_id) FROM rot_gallery_stats1 as gss \
-							WHERE gss.gallery_id = gi.gallery_id AND group_id != 0) as categories \
-							FROM rot_gallery_info AS gi \
-							JOIN rot_gallery_data1 AS gd ON gi.gallery_id = gd.gallery_id \
-							JOIN rot_gallery_stats1 AS gs ON gs.gallery_id = gi.gallery_id \
-							WHERE gi.gallery_id > ( SELECT value FROM rot_settings WHERE name = 'sphinx_max_gallery_id' ) \
-							AND gallery_status = 'active' and gallery_type = 0 \
-							and gs.best_thumb = 'yes' and gs.group_id = 0
-}
+Sphinx is not magic—it searches using its index. The index is created when you index your database. If you change the database (add, delete galleries, etc.) without reindexing Sphinx, it won't see those changes.
-index delta : your_name_index
+Reindex periodically. The best approach: schedule reindexing via cron during your lowest-traffic periods.
-{
+===== Mysql Search =====
-    source = delta
-    path = /your_full_path/data/delta
-}
+By default, search uses `WHERE description LIKE '%search_term%'`. In Rotation Settings, you'll find a "Search fields" option.
-</code>
+If you don't use Sphinx, the script uses MySQL's built-in search, which is slower. To compensate, we limit the number of fields searched. By default, the script searches in the description field. You can also search the Title (Alt) field or both, though this increases load slightly.
+Sphinx is the best option and is described above.
-You can note that it has the same query with an addition of sphinx_max_gallery_id.
+A middle-ground option is FullText Search for MySQL (the last item in the Search fields options). MySQL can search using morphology, though before version 5.6 this only worked with MyISAM tables.
-Now you have to add a new crontab task that will reindex new parts
+To use this option (easier than Sphinx to set up), add a fulltext index to the rot_gallery_data* tables:
-  indexer --rotate delta
+<code>
+        ALTER TABLE `rot_gallery_data1` ADD FULLTEXT (
+		`alt` ,
+		`description`
+		)
+</code>
-Now you have to add 'delta' into  Sphinx Delta Index  (Rotation settings)
+And switch in settings the option to FullText search.
-once a day you can marge main index and delta index
-  indexer --rotate --merge your_name_index delta
-That's it.