Using Sphinx with MySQL - High Performance MySQL - page 757

structure called a multivalued attribute , discussed later.) Each document has a unique
32-bit or 64-bit integer identifier that should be drawn from the database table being
indexed (for instance, from a primary key column). In addition, each document has
one or more full-text fields (each corresponding to a text column from the database)
and numerical attributes. Like a database table, the Sphinx index has the same fields
and attributes for all of its documents. Table F-1 shows the analogy between a database
table and a Sphinx index.

Table F-1. Database structure and corresponding Sphinx structure

Database structure Sphinx structure

CREATE TABLE documents (
id int(11) NOT NULL auto_increment,
title varchar(255),
content text,
group_id int(11),
added datetime,
PRIMARY KEY (id)
);

index documents
document ID
title field, full-text indexed
content field, full-text indexed
group_id attribute, sql_attr_uint
added attribute, sql_attr_timestamp

Sphinx does not store the text fields from the database but just uses their contents to
build a search index.

Installation Overview
Sphinx installation is straightforward and typically includes the following steps:
1. Building the programs from sources:
$ configure && make && make install
2. Creating a configuration file with definitions for data sources and full-text indexes
3. Initial indexing
4. Launching searchd
After that, the search functionality is immediately available for client programs:
<?php
include ( 'sphinxapi.php' );
$cl = new SphinxClient ();
$res = $cl->Query ( 'test query', 'myindex' );
// use $res search result here
?>
The only thing left to do is run indexer regularly to update the full-text index data.
Indexes that searchd is currently serving will stay fully functional during reindexing:
indexer will detect that they are in use, create a “shadow” index copy instead, and notify
searchd to pick up that copy on completion.
Full-text indexes are stored in the filesystem (at the location specified in the configu-
ration file) and are in a special “monolithic” format, which is not well suited for