Creating a trial search engine on Sphinx + php

Prehistory

Previously, we used the usual fulltext search to search the site. But at a certain point, he stopped arranging us and we decided to try out an alternative search technology: Sphinx. Unfortunately, the Sphinx has no Russian documentation at all, so this article is similar to the article Build a custom search engine with PHP , only in Russian and for my local environment (windows 7, mysql / php)
The article consists of 4 parts:

1. A short story about the preparation of the base for the search.
2. The story about the initial installation and configuration of the Sphinx
3. Database indexing and test search from the command line
4. Php test search

1. Prepare a database for search

Before you start using the Sphinx, it is better to immediately prepare a database for easier indexing and searching.
I have three tables: products, companies, categories, respectively products, companies and product categories. Create a view in which we merge the data from all three tables that will be searched:

CREATE OR REPLACE VIEW `catalog` AS SELECT p.`id` , p.`id_company` , p.`id_category` , p.`name` , p.`keyword` , p.`short_desc` , com.`name` AS company_name, cat.`name` AS category_name FROM `products` p JOIN `categories` cat ON p.id_category = cat.id JOIN `companies` com ON p.id_company = com.id WHERE 1 

In my case, the search is conducted by the fields: name, keyword, a brief description of the product, the name of the manufacturing company and the name of the product category. In WHERE, you can add additional conditions, for example, so that the search is only for active companies and products. At this preparatory work with the database is completed.

2. Install Sphinx

It is necessary to download the latest distribution from offsite: www.sphinxsearch.com/downloads.html
I downloaded the Win32 version of binaries w / MySQL support 1.10 beta. Next, unpack the contents of the archive in any folder (I unpacked in C: \ Sphinx)

Configuring Sphinx

After downloading and unpacking the archive, the next step is setting. I took the sphinx.conf.in file as a basis.
In this section, I’ll just give configs with a few comments, everything seems to be clear there. All the text from this section is in one sphinx.conf.in file.
')

Data source

 #     source catalog { #   #  : mysql, pgsql, mssql, xmlpipe, xmlpipe2, odbc type = mysql #        sql_host = localhost sql_user = root sql_pass = sql_db = products sql_port = 3306 # ,   3306 # -,           #       UTF-8,           sql_query_pre = SET NAMES utf8 # ,       #        ID  sql_query = \ SELECT * \ FROM catalog # ,     ,    sql_attr_uint = id_company sql_attr_uint = id_category # document info query, ONLY for CLI search (ie. testing and debugging) # optional, default is empty # must contain $id macro and must fetch the document by that id sql_query_info = SELECT * FROM products WHERE id=$id } 

Product indexing options

 #      index catalog { #     source = catalog # ,      path = C:\Sphinx/data/catalog #     morphology = stem_ru #      min_word_len = 1 #  charset_type = utf-8 } 

Settings daemon (service)

 #   () searchd { #      ""  listen = 9312 #    log = C:\Sphinx/log/searchd.log #      query_log = C:\Sphinx/log/query.log # PID file, searchd process ID file name # mandatory pid_file = C:\Sphinx/log/searchd.pid } 

This completes the configuration of the Sphinx.
Now we install our service. To do this, in the address bar write C: \ Sphinx \ bin \ searchd --install --config C: \ Sphinx \ sphinx.conf.in --servicename SphinxSearch
The service is installed and should appear in the administration - service. While not running - our data is not yet indexed.

3. Indexing and Test Search

Now on the command line, write C: \ Sphinx \ bin \ indexer --all --config C: \ Sphinx \ sphinx.conf.in . We get a result like this:

 C:\Users\Iskander> C:\Sphinx\bin\indexer --all --config C:\Sphinx\sphinx.conf.in Sphinx 1.10-beta (r2420) Copyright (c) 2001-2010, Andrew Aksyonoff Copyright (c) 2008-2010, Sphinx Technologies Inc (http://sphinxsearch.com) using config file 'C:\Sphinx\sphinx.conf.in'... indexing index 'catalog'... collected 572 docs, 0.1 MB sorted 0.0 Mhits, 100.0% done total 572 docs, 115192 bytes total 0.380 sec, 303124 bytes/sec, 1505.19 docs/sec total 2 reads, 0.000 sec, 31.6 kb/call avg, 0.0 msec/call avg total 9 writes, 0.001 sec, 14.6 kb/call avg, 0.1 msec/call avg 

Everything, the index is created. Now we start our SphinxSearch service and in the search line we write something like
C: \ Sphinx \ bin \ search --config C: \ Sphinx \ sphinx.conf.in computer and get the result

 C:\Users\Iskander> C:\Sphinx\bin\search --config C:\Sphinx\sphinx.conf.in computer Sphinx 1.10-beta (r2420) Copyright (c) 2001-2010, Andrew Aksyonoff Copyright (c) 2008-2010, Sphinx Technologies Inc (http://sphinxsearch.com) using config file 'C:\Sphinx\sphinx.conf.in'... index 'catalog': query 'computer': returned 1 matches of 1 total in 0.000 sec displaying matches: 1. document=1, weight=2812, id_company=2, id_category=1263 id=1 id_company=2 id_category=1263 words: 1. 'computer': 1 documents, 2 hits 

Search works!

4. Search from php

Well, everything is simple. For example, conduct a search for the query "Computer"

 <?php //    api include('C:\Sphinx\api\sphinxapi.php'); //   -        $cl = new SphinxClient(); $cl->SetServer( "localhost", 9312 ); //   $cl->SetMatchMode( SPH_MATCH_ANY ); //    1     $result = $cl->Query("computer"); //   //    if ( $result === false ) { echo "Query failed: " . $cl->GetLastError() . ".\n"; //     } else { if ( $cl->GetLastWarning() ) { echo "WARNING: " . $cl->GetLastWarning() . " //      "; } if ( ! empty($result["matches"]) ) { //     -   foreach ( $result["matches"] as $product => $info ) { echo $product . "<br />"; //   id   } } } exit; 

That's all, we installed sphinx, indexed our database and wrote php code that searches through our database and displays the id of the products found.

The time has come to further study the documentation , adjust the ranking as the project requires, and generally continue the transfer of functionality from full-text search to sphinx.

I hope this article will be able to help someone to do a “quick start” with a sphinx, because I think there’s a lack of normal Russian articles and documentation on this rather complex, in my opinion, product.