Sphinx Full-Text Search tutorial for PHP use

I. Introduction of Sphinx

1. What is Sphinx?

Chinese name: Full-text indexing engine. Only English and Russian are supported. However, any language can be supported as long as there is a corresponding language pack. There is a team in the country on the basis of Sphinx encapsulated a package with Chinese software: Coreseek.

2. Why use Sphinx?

In the MySQL database, for the following SQL statement,SELECT * from xxx where like xxx '%xxx '; (like query starting with%), no index optimizations can be used, resulting in very slow queries if the amount of data is very large. And this SQL statement in a lot of functions to use, such as according to the lyrics query songs, according to the plot query movies. If you want to speed up queries only use third-party software,Sphinx and lucence. The functionality of full-text indexing is also available in MySQL, but there are two issues: (1) only MyISAM engine support (2) is not good for Chinese support. However, the current version of innodb1.2 in the latest version of mysql5.6 also supports full-text indexing.

3. Sphinx Principle of Use

(1) Create a data source first.
(2) Create an index based on the data source, using word breaker technology.
(3) php to query the keyword to the Sphinx server, Sphinx based on keywords to find the key in the MySQL table in the record Id.sphinx to return the ID to the PHP query.
(4) PHP queries the MySQL server based on the returned ID.

Second, installation and use

1. Download, unzip

Official website Download: http://www.Sphinxsearch.com

Support Chinese word segmentation: http://www.coreseek.com

Unzip and copy to the specified directory, general and other environment programs in the same level directory (easy to manage)

2. Copy the configuration file

Copy the csft_mysql.conf file below the ETC directory to the top level directory and rename it to sphinx.conf

Third, the use of configuration

1. Creating indexes on query data

The main configuration sphinx.conf configuration file

(1) Configure the data source (the data being queried is the result of the SQL statement execution)

Configuration syntax:

Source data source Name {}

Note: In a configuration file, you can configure multiple data sources.

(2) The location where the index files generated by the configuration data source are stored.

Configuration syntax:

Index index's name {}

Note: The index must correspond to a data source.

(3) Configuring Sphinx Server Information

2. Create an index

Execute a program under Sphinx indexer.exe–c configuration file (full path)--all | The name of the index (--all: Creates an index file for all indexes in the configuration file. You can also use the index name to create an index file for an index only)

(1) Open the CMD window as an administrator and execute the indexer.exe command.

(2) View the created index file.

3. Start the Sphinx server

(1) Install the Sphinx Software as a service for a system.

Syntax: searchd.exe–c configuration file--install

Help can be viewed through searchd--help

(2) Start the service.

To see if the port is started:

4. Using PHP Query

(1) In PHP use, a Sphinx interface file is required.

When using Sphinx, you need to copy the sphinxapi.php file to your project.

(2) The code is as follows.

require' Sphinxapi.php ';//use Sphinx to complete the query$SC=NewSphinxclient ();//Build Client$SC->setserver (' localhost ', 9312);//set the server//$SC->query (' query keyword ', the name of the index file);$keyword= "movie";$indexname= ' movie ';$res=$SC->query ($keyword,$indexname);$ids=$res[' Matches '];$id=Array_keys($ids);$id=implode(‘,‘,$id);mysql_connect("localhost", ' root ', ' root ');mysql_query(' Use PHP ');mysql_query(' SET NAMES UTF8 ');$sql= "Select Id,title,description from the movie where ID in ($id)";$res=mysql_query($sql);$list=Array(); while($row=Mysql_fetch_assoc($res)){    $list[]=$row;}foreach($list  as $v){    Echo $v[' title ']. ' <br/> '.$v[' description ']. ' ;}

Four, matching mode

1. Sph_match_all: Match all the words exactly

such as "Winter Snow", and will not match "I love Winter", but can match "my friends, love winter, and snow."

Because "winter snow" is divided into "winter", "the", "snow" three words, the match condition is to contain these three words at the same time, "I Love Winter" contains only one "winter"

2. Sph_match_phrase: must match entire phrase

such as "Winter Snow", will not match "my friends, love winter, and snow", although all contain the same need to strictly match no longer forgetful, only match "winter Snow"

3. Sph_match_any: Match any word

such as "Winter Snow", and will match "I love winter."

"Winter Snow"-"Winter" "Snow"

Because "I love winter" there is a "winter" match.

4. sph_match_extended: Support for some extended syntax

Support @ field Query

For example, the query title contains the ABC, content contains the BCD:

' @title ABC @content BCD '

Sph_match_boolean: With, or, not, group &,or,!, ()

such as: Hello | World

Check "Phone", or "winter"

Five, found the keyword added style display

Main use: buildexcerpts: Create document Summary, add style display to keywords.

Vi. increasing the Index

1. The principle of incremental indexing

2. How to Implement

(1) Create a new table that records the ID of the last record that has been created for the previous index
(2) When indexing, and then fetching all data greater than the ID from the database, these are the new data and then create a small index file.
(3) The small index generated by the incremental data is merged into the main index file.
(4) Update the ID of the last record to the table created in the first step.

3. Implementation steps

(1) Create a table that records the ID of the last record to create a good index

(2) Modify the Sphinx configuration file, the data source of the primary index

(3) The data source that created the incremental index, and the location of the Delta index file store

4. Stop the Sphinx server and delete the original index file.

(1) Create primary index

(2) Check if the maximum ID is recorded in a table

5. Start the Sphinx server and perform a query test.

6. Add a new movie.

7. Create an incremental index.

8. Merge the created incremental index into the main index.

Syntax used:INDEXER-C profile--merge primary index file Delta index file--rotate--rotate forced merge, can be merged without shutting down Sphinx services.

Sphinx Full-Text Search tutorial for PHP use