Differences

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

--- new_rotation_sphinx [2025/02/15 11:34] – [Sphinx search] admin
+++ new_rotation_sphinx [2026/03/21 10:11] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Sphinx search ======
+====== Sphinx for Rotation\Tube sites ======
-Basically script uses mysql to search DB, but with large DB mysql is too slow. It also doesn't 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.
-**Workflow:**
+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.
-  * You have a DB with galleries
+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.
-  * Sphinx creates a "zip" version of your DB
-  * SmartCJ queries Sphinx to search for keywords
-  * Sphinx does a search and returns results
-  * SmartCJ outputs results
-**Setup:**
+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.
-  * Ask admin to setup sphinx
+You can also index your search query log with Sphinx to find similar searches (Sphinx Search Log Index).
-  * switch from regular search to Sphinx (Rotation - Settings - Site Search )
-  * ask admin to add scj index into sphinx config. (Copy config from Rotation - Settings - Site Search config example  )
-  * Run indexer (or ask admin to do it) <code> indexer --all --rotate </code>
-  * In Rotation - Settings - Site search make sure you added Sphinx settings (Sphinx Host, Port, index name - add you real data, ask admin if you don't know it)
-That's it.
+Sphinx offers different search modes (Sphinx Search Mod) and result ranking options (Sphinx Ranker Mod). See the Sphinx documentation for details.
-**Note** if you change DB (add\remove content) - please, run indexer every day to keep it's base up to date.
+Common search terms can return huge result sets with excessive pagination. Sphinx Max Matches lets you limit the maximum number of results returned.
-Add to cron something like (ask admin)
+===== 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 32: / Line 36: @@
-====== Sphinx config ======
+===== Sphinx Delta Config =====
-Please, open Rotation Settings - Site Search , change search engine to sphinx and the script will generate a config for you.
+The Sphinx index captures your database at a point in time. Any changes made after indexing won't be discoverable by Sphinx.
+This means you'd need to reindex after every database change, but full reindexing is time-consuming and resource-intensive on large databases.
-====== Sphinx Delta index ======
+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.
-If you have a large DB it might take time to reindex the entire DB, so you can use
+Sphinx searches both the main index and delta index together, showing all changes. You can create delta indexes as frequently as needed.
-Now you have to add a new crontab task that will reindex new parts
+Result:
+in the main part which you already have you need to add after
+  sql_query_pre = SET NAMES utf8
+line
+  sql_query_pre = UPDATE rot_settings SET value = (SELECT MAX(gallery_id) FROM rot_gallery_info) WHERE name = 'sphinx_max_gallery_id'
+Now you need to add the creation of a delta index
+<code>
+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 delta : your_name_index
+{
+    source = delta
+    path = /your_full_path/data/delta
+}
+</code>
+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
-Now you have to add 'delta' into  Sphinx Delta Index  (Rotation settings)
+After creating the index you need to specify delta in the Sphinx Delta Index settings of rotation.
-once a day you can marge main index and delta index
+and once a day for example you can attach the delta index to the main one
   indexer --rotate --merge your_name_index delta
-That's it.
+All.
-====== Sphinx and slave sites ======
-Slave site automatically use master's search settings. No need to do anything extra at slave sites.
+===== 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 search_queries
+{
+	type					= mysql
+	sql_host				= YOUR_HOST
+	sql_user				= USERNAME
+	sql_pass				= PASSWORD
+	sql_db					= DB_NAME
+	sql_port				= 3306	# optional, default is 3306
+    sql_query_pre = SET NAMES utf8
+	sql_query				= SELECT sq_id, search_query, hits, items_found FROM rot_search_queries WHERE hits > 0  GROUP BY search_query
+	sql_attr_uint		    = hits
+	sql_attr_uint		    = items_found
+}
+index search_queries_index
+{
+	source					= search_queries
+	path					= /WHERE_TO_STORE
+	docinfo					= extern
+	morphology              = stem_en
+}
+</code>
+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.
+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.
+===== Sphinx settings =====
+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
+===== Sphinx for child (slave) sites =====
+Slaves use Sphinx data from the master's database by default. If Sphinx is set up on the master, all slaves automatically use it.
+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.
+===== Sphinx Search CTR  =====
+Sphinx search defaults to relevance sorting (domain/?search=...). You can also sort by date (domain/?search=...&order=date) or duration (domain/?search=...&order=duration).
+===== Sphinx settings =====
+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 "*"
+But of course it's best to go through the Sphinx documentation and choose the parameters for yourself.
+===== Sphinx Reindex =====
+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.
+Reindex periodically. The best approach: schedule reindexing via cron during your lowest-traffic periods.
+===== Mysql Search =====
+By default, search uses `WHERE description LIKE '%search_term%'`. In Rotation Settings, you'll find a "Search fields" option.
+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.
+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.
+To use this option (easier than Sphinx to set up), add a fulltext index to the rot_gallery_data* tables:
+<code>
+        ALTER TABLE `rot_gallery_data1` ADD FULLTEXT (
+		`alt` ,
+		`description`
+		)
+</code>
+And switch in settings the option to FullText search.