====== Sphinx for Rotation\Tube sites ======
When your database exceeds 100,000 galleries, standard MySQL search becomes slow.
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.
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.
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.
You can also index your search query log with Sphinx to find similar searches (Sphinx Search Log Index).
Sphinx offers different search modes (Sphinx Search Mod) and result ranking options (Sphinx Ranker Mod). See the Sphinx documentation for details.
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:
indexer --all --rotate
===== Sphinx Delta Config =====
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.
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.
Sphinx searches both the main index and delta index together, showing all changes. You can create delta indexes as frequently as needed.
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
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
}
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
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
}
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 ``, 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:
ALTER TABLE `rot_gallery_data1` ADD FULLTEXT (
`alt` ,
`description`
)
And switch in settings the option to FullText search.