Sphinx 0.9.9-rc2 reference manual

Generally, it's a standalone search engine, meant to provide fast, size-efficient and relevant full-text search functions to other applications. Sphinx was specially designed to integrate well with SQL databases and scripting languages.

Currently built-in data source drivers support fetching data either via direct connection to MySQL, or PostgreSQL, or from a pipe in a custom XML format. Adding new drivers (eg. to natively support some other DBMSes) is designed to be as easy as possible.

Search API is natively ported to PHP, Python, Perl, Ruby, Java, and also available as a pluggable MySQL storage engine. API is very lightweight so porting it to new language is known to take a few hours.

As for the name, Sphinx is an acronym which is officially decoded as SQL Phrase Index. Yes, I know about CMU's Sphinx project.

1.2. Sphinx features

high indexing speed (upto 10 MB/sec on modern CPUs);
high search speed (avg query is under 0.1 sec on 2-4 GB text collections);
high scalability (upto 100 GB of text, upto 100 M documents on a single CPU);
provides good relevance ranking through combination of phrase proximity ranking and statistical (BM25) ranking;
provides distributed searching capabilities;
provides document exceprts generation;
provides searching from within MySQL through pluggable storage engine;
supports boolean, phrase, and word proximity queries;
supports multiple full-text fields per document (upto 32 by default);
supports multiple additional attributes per document (ie. groups, timestamps, etc);
supports stopwords;
supports both single-byte encodings and UTF-8;
supports English stemming, Russian stemming, and Soundex for morphology;
supports MySQL natively (MyISAM and InnoDB tables are both supported);
supports PostgreSQL natively.

1.3. Where to get Sphinx

Sphinx is available through its official Web site at http://www.sphinxsearch.com/.

Currently, Sphinx distribution tarball includes the following software:

indexer: an utility which creates fulltext indexes;
search: a simple command-line (CLI) test utility which searches through fulltext indexes;
searchd: a daemon which enables external software (eg. Web applications) to search through fulltext indexes;
sphinxapi: a set of searchd client API libraries for popular Web scripting languages (PHP, Python, Perl, Ruby).
spelldump: a simple command-line tool to extract the items from an ispell or MySpell (as bundled with OpenOffice) format dictionary to help customize your index, for use with wordforms.
indextool: an utility to dump miscellaneous debug information about the index, added in version 0.9.9-rc2.

1.4. License

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. See COPYING file for details.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

If you don't want to be bound by GNU GPL terms (for instance, if you would like to embed Sphinx in your software, but would not like to disclose its source code), please contact the author to obtain a commercial license.

1.5. Author and contributors

Author

Sphinx initial author and current primary developer is:

Andrew Aksyonoff, <shodan(at)shodan.ru>

Contributors

People who contributed to Sphinx and their contributions (in no particular order) are:

Robert "coredev" Bengtsson (Sweden), initial version of PostgreSQL data source;
Len Kranendonk, Perl API
Dmytro Shteflyuk, Ruby API

Many other people have contributed ideas, bug reports, fixes, etc. Thank you!

1.6. History

Sphinx development was started back in 2001, because I didn't manage to find an acceptable search solution (for a database driven Web site) which would meet my requirements. Actually, each and every important aspect was a problem:

search quality (ie. good relevance)
- statistical ranking methods performed rather bad, especially on large collections of small documents (forums, blogs, etc)
search speed
- especially if searching for phrases which contain stopwords, as in "to be or not to be"
moderate disk and CPU requirements when indexing
- important in shared hosting enivronment, not to mention the indexing speed.

Despite the amount of time passed and numerous improvements made in the other solutions, there's still no solution which I personally would be eager to migrate to.

Considering that and a lot of positive feedback received from Sphinx users during last years, the obvious decision is to continue developing Sphinx (and, eventually, to take over the world).

2. Installation

2.1. Supported systems

Most modern UNIX systems with a C++ compiler should be able to compile and run Sphinx without any modifications.

Currently known systems Sphinx has been successfully running on are:

Linux 2.4.x, 2.6.x (various distributions)
Windows 2000, XP
FreeBSD 4.x, 5.x, 6.x
NetBSD 1.6, 3.0
Solaris 9, 11
Mac OS X

CPU architectures known to work include X86, X86-64, SPARC64.

I hope Sphinx will work on other Unix platforms as well. If the platform you run Sphinx on is not in this list, please do report it.

At the moment, Windows version of Sphinx is not intended to be used in production, but rather for testing and debugging only. Two most prominent issues are missing concurrent queries support (client queries are stacked on TCP connection level instead), and missing index data rotation support. There are succesful production installations which workaround these issues. However, running high-volume search service under Windows is still not recommended.

2.2. Required tools

On UNIX, you will need the following tools to build and install Sphinx:

a working C++ compiler. GNU gcc is known to work.
a good make program. GNU make is known to work.

On Windows, you will need Microsoft Visual C/C++ Studio .NET 2003 or 2005. Other compilers/environments will probably work as well, but for the time being, you will have to build makefile (or other environment specific project files) manually.

2.3. Installing Sphinx on Linux

Extract everything from the distribution tarball (haven't you already?) and go to the sphinx subdirectory:
$ tar xzvf sphinx-0.9.8.tar.gz $ cd sphinx
Run the configuration program:
$ ./configure
There's a number of options to configure. The complete listing may be obtained by using --help switch. The most important ones are:
- --prefix, which specifies where to install Sphinx; such as --prefix=/usr/local/sphinx (all of the examples use this prefix)
- --with-mysql, which specifies where to look for MySQL include and library files, if auto-detection fails;
- --with-pgsql, which specifies where to look for PostgreSQL include and library files.
Build the binaries:
$ make
Install the binaries in the directory of your choice: (defaults to /usr/local/bin/ on *nix systems, but is overridden with configure --prefix)
$ make install

2.4. Installing Sphinx on Windows

Installing Sphinx on a Windows server is often easier than installing on a Linux environment; unless you are preparing code patches, you can use the pre-compiled binary files from the Downloads area on the website.

Extract everything from the .zip file you have downloaded - sphinx-0.9.8-win32.zip (or sphinx-0.9.8-win32-pgsql.zip if you need PostgresSQL support as well.) You can use Windows Explorer in Windows XP and up to extract the files, or a freeware package like 7Zip to open the archive.
For the remainder of this guide, we will assume that the folders are unzipped into C:\Sphinx, such that searchd.exe can be found in C:\Sphinx\bin\searchd.exe. If you decide to use any different location for the folders or configuration file, please change it accordingly.
Install the searchd system as a Windows service:
C:\Sphinx> C:\Sphinx\searchd --install --config C:\Sphinx\sphinx.conf --servicename SphinxSearch
The searchd service will now be listed in the Services panel within the Management Console, available from Administrative Tools. It will not have been started, as you will need to configure it and build your indexes with indexer before starting the service. A guide to do this can be found under Quick tour.

2.5. Known installation issues

If configure fails to locate MySQL headers and/or libraries, try checking for and installing mysql-devel package. On some systems, it is not installed by default.

If make fails with a message which look like

/bin/sh: g++: command not found
make[1]: *** [libsphinx_a-sphinx.o] Error 127

try checking for and installing gcc-c++ package.

If you are getting compile-time errors which look like

sphinx.cpp:67: error: invalid application of `sizeof' to
    incomplete type `Private::SizeError<false>'

this means that some compile-time type size check failed. The most probable reason is that off_t type is less than 64-bit on your system. As a quick hack, you can edit sphinx.h and replace off_t with DWORD in a typedef for SphOffset_t, but note that this will prohibit you from using full-text indexes larger than 2 GB. Even if the hack helps, please report such issues, providing the exact error message and compiler/OS details, so I could properly fix them in next releases.

If you keep getting any other error, or the suggestions above do not seem to help you, please don't hesitate to contact me.

2.6. Quick Sphinx usage tour

All the example commands below assume that you installed Sphinx in /usr/local/sphinx, so searchd can be found in /usr/local/sphinx/bin/searchd.

To use Sphinx, you will need to:

Create a configuration file.
Default configuration file name is sphinx.conf. All Sphinx programs look for this file in current working directory by default.
Sample configuration file, sphinx.conf.dist, which has all the options documented, is created by configure. Copy and edit that sample file to make your own configuration: (assuming Sphinx is installed into /usr/local/sphinx/)
$ cd /usr/local/sphinx/etc $ cp sphinx.conf.dist sphinx.conf $ vi sphinx.conf
Sample configuration file is setup to index documents table from MySQL database test; so there's example.sql sample data file to populate that table with a few documents for testing purposes:
$ mysql -u test < /usr/local/sphinx/etc/example.sql
Run the indexer to create full-text index from your data:
$ cd /usr/local/sphinx/etc $ /usr/local/sphinx/bin/indexer
Query your newly created index!

To query the index from command line, use search utility:

$ cd /usr/local/sphinx/etc $ /usr/local/sphinx/bin/search test

To query the index from your PHP scripts, you need to:

Run the search daemon which your script will talk to:
$ cd /usr/local/sphinx/etc $ /usr/local/sphinx/bin/searchd
Run the attached PHP API test script (to ensure that the daemon was succesfully started and is ready to serve the queries):
$ cd sphinx/api $ php test.php test
Include the API (it's located in api/sphinxapi.php) into your own scripts and use it.

Happy searching!

3. Indexing

3.1. Data sources

The data to be indexed can generally come from very different sources: SQL databases, plain text files, HTML files, mailboxes, and so on. From Sphinx point of view, the data it indexes is a set of structured documents, each of which has the same set of fields. This is biased towards SQL, where each row correspond to a document, and each column to a field.

Depending on what source Sphinx should get the data from, different code is required to fetch the data and prepare it for indexing. This code is called data source driver (or simply driver or data source for brevity).

At the time of this writing, there are drivers for MySQL and PostgreSQL databases, which can connect to the database using its native C/C++ API, run queries and fetch the data. There's also a driver called xmlpipe, which runs a specified command and reads the data from its stdout. See Section 3.8, “xmlpipe data source” section for the format description.

There can be as many sources per index as necessary. They will be sequentially processed in the very same order which was specifed in index definition. All the documents coming from those sources will be merged as if they were coming from a single source.

3.2. Attributes

Attributes are additional values associated with each document that can be used to perform additional filtering and sorting during search.

It is often desired to additionally process full-text search results based not only on matching document ID and its rank, but on a number of other per-document values as well. For instance, one might need to sort news search results by date and then relevance, or search through products within specified price range, or limit blog search to posts made by selected users, or group results by month. To do that efficiently, Sphinx allows to attach a number of additional attributes to each document, and store their values in the full-text index. It's then possible to use stored values to filter, sort, or group full-text matches.

Attributes, unlike the fields, are not full-text indexed. They are stored in the index, but it is not possible to search them as full-text, and attempting to do so results in an error.

For example, it is impossible to use the extended matching mode expression @column 1 to match documents where column is 1, if column is an attribute, and this is still true even if the numeric digits are normally indexed.

Attributes can be used for filtering, though, to restrict returned rows, as well as sorting or result grouping; it is entirely possible to sort results purely based on attributes, and ignore the search relevance tools. Additionally, attributes are returned from the search daemon, while the indexed text is not.

A good example for attributes would be a forum posts table. Assume that only title and content fields need to be full-text searchable - but that sometimes it is also required to limit search to a certain author or a sub-forum (ie. search only those rows that have some specific values of author_id or forum_id columns in the SQL table); or to sort matches by post_date column; or to group matching posts by month of the post_date and calculate per-group match counts.

This can be achieved by specifying all the mentioned columns (excluding title and content, that are full-text fields) as attributes, indexing them, and then using API calls to setup filtering, sorting, and grouping. Here as an example.

Example sphinx.conf part:

...
sql_query = SELECT id, title, content, \
	author_id, forum_id, post_date FROM my_forum_posts
sql_attr_uint = author_id
sql_attr_uint = forum_id
sql_attr_timestamp = post_date
...

Example application code (in PHP):

// only search posts by author whose ID is 123
$cl->SetFilter ( "author_id", array ( 123 ) );

// only search posts in sub-forums 1, 3 and 7
$cl->SetFilter ( "forum_id", array ( 1,3,7 ) );

// sort found posts by posting date in descending order
$cl->SetSortMode ( SPH_SORT_ATTR_DESC, "post_date" );

Attributes are named. Attribute names are case insensitive. Attributes are not full-text indexed; they are stored in the index as is. Currently supported attribute types are:

unsigned integers (1-bit to 32-bit wide);
UNIX timestamps;
floating point values (32-bit, IEEE 754 single precision);
string ordinals (specially computed integers);
MVA, multi-value attributes (variable-length lists of 32-bit unsigned integers).

The complete set of per-document attribute values is sometimes referred to as docinfo. Docinfos can either be

stored separately from the main full-text index data ("extern" storage, in .spa file), or
attached to each occurence of document ID in full-text index data ("inline" storage, in .spd file).

When using extern storage, a copy of .spa file (with all the attribute values for all the documents) is kept in RAM by searchd at all times. This is for performance reasons; random disk I/O would be too slow. On the contrary, inline storage does not require any additional RAM at all, but that comes at the cost of greatly inflating the index size: remember that it copies all attribute value every time when the document ID is mentioned, and that is exactly as many times as there are different keywords in the document. Inline may be the only viable option if you have only a few attributes and need to work with big datasets in limited RAM. However, in most cases extern storage makes both indexing and searching much more efficient.

Search-time memory requirements for extern storage are (1+number_of_attrs)*number_of_docs*4 bytes, ie. 10 million docs with 2 groups and 1 timestamp will take (1+2+1)*10M*4 = 160 MB of RAM. This is PER DAEMON, not per query. searchd will allocate 160 MB on startup, read the data and keep it shared between queries. The children will NOT allocate any additional copies of this data.

3.3. MVA (multi-valued attributes)

MVAs, or multi-valued attributes, are an important special type of per-document attributes in Sphinx. MVAs make it possible to attach lists of values to every document. They are useful for article tags, product categories, etc. Filtering and group-by (but not sorting) on MVA attributes is supported.

Currently, MVA list entries are limited to unsigned 32-bit integers. The list length is not limited, you can have an arbitrary number of values attached to each document as long as RAM permits (.spm file that contains the MVA values will be precached in RAM by searchd). The source data can be taken either from a separate query, or from a document field; see source type in sql_attr_multi. In the first case the query will have to return pairs of document ID and MVA values, in the second one the field will be parsed for integer values. There are absolutely no requirements as to incoming data order; the values will be automatically grouped by document ID (and internally sorted within the same ID) during indexing anyway.

When filtering, a document will match the filter on MVA attribute if any of the values satisfy the filtering condition. (Therefore, documents that pass through exclude filters will not contain any of the forbidden values.) When grouping by MVA attribute, a document will contribute to as many groups as there are different MVA values associated with that document. For instance, if the collection contains exactly 1 document having a 'tag' MVA with values 5, 7, and 11, grouping on 'tag' will produce 3 groups with '@count' equal to 1 and '@groupby' key values of 5, 7, and 11 respectively. Also note that grouping by MVA might lead to duplicate documents in the result set: because each document can participate in many groups, it can be chosen as the best one in in more than one group, leading to duplicate IDs. PHP API historically uses ordered hash on the document ID for the resulting rows; so you'll also need to use SetArrayResult() in order to employ group-by on MVA with PHP API.

3.4. Indexes

To be able to answer full-text search queries fast, Sphinx needs to build a special data structure optimized for such queries from your text data. This structure is called index; and the process of building index from text is called indexing.

Different index types are well suited for different tasks. For example, a disk-based tree-based index would be easy to update (ie. insert new documents to existing index), but rather slow to search. Therefore, Sphinx architecture allows for different index types to be implemented easily.

The only index type which is implemented in Sphinx at the moment is designed for maximum indexing and searching speed. This comes at a cost of updates being really slow; theoretically, it might be slower to update this type of index than than to reindex it from scratch. However, this very frequently could be worked around with muiltiple indexes, see Section 3.10, “Live index updates” for details.

It is planned to implement more index types, including the type which would be updateable in real time.

There can be as many indexes per configuration file as necessary. indexer utility can reindex either all of them (if --all option is specified), or a certain explicitly specified subset. searchd utility will serve all the specified indexes, and the clients can specify what indexes to search in run time.

3.5. Restrictions on the source data

There are a few different restrictions imposed on the source data which is going to be indexed by Sphinx, of which the single most important one is:

ALL DOCUMENT IDS MUST BE UNIQUE UNSIGNED NON-ZERO INTEGER NUMBERS (32-BIT OR 64-BIT, DEPENDING ON BUILD TIME SETTINGS).

If this requirement is not met, different bad things can happen. For instance, Sphinx can crash with an internal assertion while indexing; or produce strange results when searching due to conflicting IDs. Also, a 1000-pound gorilla might eventually come out of your display and start throwing barrels at you. You've been warned.

3.6. Charsets, case folding, and translation tables

When indexing some index, Sphinx fetches documents from the specified sources, splits the text into words, and does case folding so that "Abc", "ABC" and "abc" would be treated as the same word (or, to be pedantic, term).

To do that properly, Sphinx needs to know

what encoding is the source text in;
what characters are letters and what are not;
what letters should be folded to what letters.

This should be configured on a per-index basis using charset_type and charset_table options. charset_type specifies whether the document encoding is single-byte (SBCS) or UTF-8. charset_table specifies the table that maps letter characters to their case folded versions. The characters that are not in the table are considered to be non-letters and will be treated as word separators when indexing or searching through this index.

Note that while default tables do not include space character (ASCII code 0x20, Unicode U+0020) as a letter, it's in fact perfectly legal to do so. This can be useful, for instance, for indexing tag clouds, so that space-separated word sets would index as a single search query term.

Default tables currently include English and Russian characters. Please do submit your tables for other languages!

3.7. SQL data sources (MySQL, PostgreSQL)

With all the SQL drivers, indexing generally works as follows.

connection to the database is established;
pre-query (see Section 9.1.11, “sql_query_pre”) is executed to perform any necessary initial setup, such as setting per-connection encoding with MySQL;
main query (see Section 9.1.12, “sql_query”) is executed and the rows it returns are indexed;
post-query (see Section 9.1.23, “sql_query_post”) is executed to perform any necessary cleanup;
connection to the database is closed;
indexer does the sorting phase (to be pedantic, index-type specific post-processing);
connection to the database is established again;
post-index query (see Section 9.1.24, “sql_query_post_index”) is executed to perform any necessary final cleanup;
connection to the database is closed again.

Most options, such as database user/host/password, are straightforward. However, there are a few subtle things, which are discussed in more detail here.

Ranged queries

Main query, which needs to fetch all the documents, can impose a read lock on the whole table and stall the concurrent queries (eg. INSERTs to MyISAM table), waste a lot of memory for result set, etc. To avoid this, Sphinx supports so-called ranged queries. With ranged queries, Sphinx first fetches min and max document IDs from the table, and then substitutes different ID intervals into main query text and runs the modified query to fetch another chunk of documents. Here's an example.

Example 1. Ranged query usage example

# in sphinx.conf

sql_query_range	= SELECT MIN(id),MAX(id) FROM documents
sql_range_step = 1000
sql_query = SELECT * FROM documents WHERE id>=$start AND id<=$end

If the table contains document IDs from 1 to, say, 2345, then sql_query would be run three times:

with $start replaced with 1 and $end replaced with 1000;
with $start replaced with 1001 and $end replaced with 2000;
with $start replaced with 2000 and $end replaced with 2345.

Obviously, that's not much of a difference for 2000-row table, but when it comes to indexing 10-million-row MyISAM table, ranged queries might be of some help.

`sql_post` vs. `sql_post_index`

The difference between post-query and post-index query is in that post-query is run immediately when Sphinx received all the documents, but further indexing may still fail for some other reason. On the contrary, by the time the post-index query gets executed, it is guaranteed that the indexing was succesful. Database connection is dropped and re-established because sorting phase can be very lengthy and would just timeout otherwise.

3.8. xmlpipe data source

xmlpipe data source was designed to enable users to plug data into Sphinx without having to implement new data sources drivers themselves. It is limited to 2 fixed fields and 2 fixed attributes, and is deprecated in favor of Section 3.9, “xmlpipe2 data source” now. For new streams, use xmlpipe2.

To use xmlpipe, configure the data source in your configuration file as follows:

source example_xmlpipe_source
{
    type = xmlpipe
    xmlpipe_command = perl /www/mysite.com/bin/sphinxpipe.pl
}

The indexer will run the command specified in xmlpipe_command, and then read, parse and index the data it prints to stdout. More formally, it opens a pipe to given command and then reads from that pipe.

indexer will expect one or more documents in custom XML format. Here's the example document stream, consisting of two documents:

Example 2. XMLpipe document stream

<document>
<id>123</id>
<group>45</group>
<timestamp>1132223498</timestamp>
<title>test title</title>
<body>
this is my document body
</body>
</document>

<document>
<id>124</id>
<group>46</group>
<timestamp>1132223498</timestamp>
<title>another test</title>
<body>
this is another document
</body>
</document>

Legacy xmlpipe legacy driver uses a builtin parser which is pretty fast but really strict and does not actually fully support XML. It requires that all the fields must be present, formatted exactly as in this example, and occur exactly in the same order. The only optional field is timestamp; it defaults to 1.

3.9. xmlpipe2 data source

xmlpipe2 lets you pass arbitrary full-text and attribute data to Sphinx in yet another custom XML format. It also allows to specify the schema (ie. the set of fields and attributes) either in the XML stream itself, or in the source settings.

When indexing xmlpipe2 source, indexer runs the given command, opens a pipe to its stdout, and expects well-formed XML stream. Here's sample stream data:

Example 3. xmlpipe2 document stream

<?xml version="1.0" encoding="utf-8"?>
<sphinx:docset>

<sphinx:schema>
<sphinx:field name="subject"/> 
<sphinx:field name="content"/>
<sphinx:attr name="published" type="timestamp"/>
<sphinx:attr name="author_id" type="int" bits="16" default="1"/>
</sphinx:schema>

<sphinx:document id="1234">
<content>this is the main content <![CDATA[[and this <cdata> entry
must be handled properly by xml parser lib]]></content>
<published>1012325463</published>
<subject>note how field/attr tags can be
in <b class="red">randomized</b> order</subject>
<misc>some undeclared element</misc>
</sphinx:document>

<!-- ... more documents here ... -->

</sphinx:docset>

Arbitrary fields and attributes are allowed. They also can occur in the stream in arbitrary order within each document; the order is ignored. There is a restriction on maximum field length; fields longer than 2 MB will be truncated to 2 MB (this limit can be changed in the source).

The schema, ie. complete fields and attributes list, must be declared before any document could be parsed. This can be done either in the configuration file using xmlpipe_field and xmlpipe_attr_XXX settings, or right in the stream using <sphinx:schema> element. <sphinx:schema> is optional. It is only allowed to occur as the very first sub-element in <sphinx:docset>. If there is no in-stream schema definition, settings from the configuration file will be used. Otherwise, stream settings take precedence.

Unknown tags (which were not declared neither as fields nor as attributes) will be ignored with a warning. In the example above, <misc> will be ignored. All embedded tags and their attributes (such as <b> in <subject> in the example above) will be silently ignored.

Support for incoming stream encodings depends on whether iconv is installed on the system. xmlpipe2 is parsed using libexpat parser that understands US-ASCII, ISO-8859-1, UTF-8 and a few UTF-16 variants natively. Sphinx configure script will also check for libiconv presence, and utilize it to handle other encodings. libexpat also enforces the requirement to use UTF-8 charset on Sphinx side, because the parsed data it returns is always in UTF-8.

XML elements (tags) recognized by xmlpipe2 (and their attributes where applicable) are:

sphinx:docset

Mandatory top-level element, denotes and contains xmlpipe2 document set.

sphinx:schema

Optional element, must either occur as the very first child of sphinx:docset, or never occur at all. Declares the document schema. Contains field and attribute declarations. If present, overrides per-source settings from the configuration file.

sphinx:field

Optional element, child of sphinx:schema. Declares a full-text field. The only recognized attribute is "name", it specifies the element name that should be treated as a full-text field in the subsequent documents.

sphinx:attr

Optional element, child of sphinx:schema. Declares an attribute. Known attributes are:

"name", specifies the element name that should be treated as an attribute in the subsequent documents.
"type", specifies the attribute type. Possible values are "int", "timestamp", "str2ordinal", "bool", "float" and "multi".
"bits", specifies the bit size for "int" attribute type. Valid values are 1 to 32.
"default", specifies the default value for this attribute that should be used if the attribute's element is not present in the document.

sphinx:document

Mandatory element, must be a child of sphinx:docset. Contains arbitrary other elements with field and attribute values to be indexed, as declared either using sphinx:field and sphinx:attr elements or in the configuration file. The only known attribute is "id" that must contain the unique integer document ID.

3.10. Live index updates

There's a frequent situation when the total dataset is too big to be reindexed from scratch often, but the amount of new records is rather small. Example: a forum with a 1,000,000 archived posts, but only 1,000 new posts per day.

In this case, "live" (almost real time) index updates could be implemented using so called "main+delta" scheme.

The idea is to set up two sources and two indexes, with one "main" index for the data which only changes rarely (if ever), and one "delta" for the new documents. In the example above, 1,000,000 archived posts would go to the main index, and newly inserted 1,000 posts/day would go to the delta index. Delta index could then be reindexed very frequently, and the documents can be made available to search in a matter of minutes.

Specifying which documents should go to what index and reindexing main index could also be made fully automatical. One option would be to make a counter table which would track the ID which would split the documents, and update it whenever the main index is reindexed.

Example 4. Fully automated live updates

# in MySQL
CREATE TABLE sph_counter
(
    counter_id INTEGER PRIMARY KEY NOT NULL,
    max_doc_id INTEGER NOT NULL
);

# in sphinx.conf
source main
{
    # ...
    sql_query_pre = SET NAMES utf8
    sql_query_pre = REPLACE INTO sph_counter SELECT 1, MAX(id) FROM documents
    sql_query = SELECT id, title, body FROM documents \
        WHERE id<=( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
}

source delta : main
{
    sql_query_pre = SET NAMES utf8
    sql_query = SELECT id, title, body FROM documents \
        WHERE id>( SELECT max_doc_id FROM sph_counter WHERE counter_id=1 )
}

index main
{
    source = main
    path = /path/to/main
    # ... all the other settings
}

# note how all other settings are copied from main,
# but source and path are overridden (they MUST be)
index delta : main
{
    source = delta
    path = /path/to/delta
}

Note how we're overriding sql_query_pre in the delta source. We need to explicitly have that override. Otherwise REPLACE query would be run when indexing delta source too, effectively nullifying it. However, when we issue the directive in the inherited source for the first time, it removes all inherited values, so the encoding setup is also lost. So sql_query_pre in the delta can not just be empty; and we need to issue the encoding setup query explicitly once again.

3.11. Index merging

Merging two existing indexes can be more efficient that indexing the data from scratch, and desired in some cases (such as merging 'main' and 'delta' indexes instead of simply reindexing 'main' in 'main+delta' partitioning scheme). So indexer has an option to do that. Merging the indexes is normally faster than reindexing but still not instant on huge indexes. Basically, it will need to read the contents of both indexes once and write the result once. Merging 100 GB and 1 GB index, for example, will result in 202 GB of IO (but that's still likely less than the indexing from scratch requires).

The basic command syntax is as follows:

indexer --merge DSTINDEX SRCINDEX [--rotate]

Only the DSTINDEX index will be affected: the contents of SRCINDEX will be merged into it. --rotate switch will be required if DSTINDEX is already being served by searchd. The initially devised usage pattern is to merge a smaller update from SRCINDEX into DSTINDEX. Thus, when merging the attributes, values from SRCINDEX will win if duplicate document IDs are encountered. Note, however, that the "old" keywords will not be automatically removed in such cases. For example, if there's a keyword "old" associated with document 123 in DSTINDEX, and a keyword "new" associated with it in SRCINDEX, document 123 will be found by both keywords after the merge. You can supply an explicit condition to remove documents from DSTINDEX to mitigate that; the relevant switch is --merge-dst-range:

indexer --merge main delta --merge-dst-range deleted 0 0

This switch lets you apply filters to the destination index along with merging. There can be several filters; all of their conditions must be met in order to include the document in the resulting mergid index. In the example above, the filter passes only those records where 'deleted' is 0, eliminating all records that were flagged as deleted (for instance, using UpdateAttributes() call).

4. Searching

4.1. Matching modes

There are the following matching modes available:

SPH_MATCH_ALL, matches all query words (default mode);
SPH_MATCH_ANY, matches any of the query words;
SPH_MATCH_PHRASE, matches query as a phrase, requiring perfect match;
SPH_MATCH_BOOLEAN, matches query as a boolean expression (see Section 4.2, “Boolean query syntax”);
SPH_MATCH_EXTENDED, matches query as an expression in Sphinx internal query language (see Section 4.3, “Extended query syntax”). As of 0.9.9, this has been superceded by SPH_MATCH_EXTENDED2, providing additional functionality and better performance. The ident is retained for legacy application code that will continue to be compatible once Sphinx and its components, including the API, are upgraded.
SPH_MATCH_EXTENDED2, matches query using the second version of the Extended matching mode.
SPH_MATCH_FULLSCAN, matches query, forcibly using the "full scan" mode as below. NB, any query terms will be ignored, such that filters, filter-ranges and grouping will still be applied, but no text-matching.

The SPH_MATCH_FULLSCAN mode will be automatically activated in place of the specified matching mode when the following conditions are met:

The query string is empty (ie. its length is zero).
docinfo storage is set to extern.

In full scan mode, all the indexed documents will be considered as matching. Such queries will still apply filters, sorting, and group by, but will not perform any full-text searching. This can be useful to unify full-text and non-full-text searching code, or to offload SQL server (there are cases when Sphinx scans will perform better than analogous MySQL queries). An example of using the full scan mode might be to find posts in a forum. By selecting the forum's user ID via SetFilter() but not actually providing any search text, Sphinx will match every document (i.e. every post) where SetFilter() would match - in this case providing every post from that user. By default this will be ordered by relevancy, followed by Sphinx document ID in ascending order (earliest first).

4.2. Boolean query syntax

Boolean queries allow the following special operators to be used:

explicit operator AND:
```
hello & world
```
operator OR:
```
hello | world
```
operator NOT:
```
hello -world
hello !world
```
grouping:
```
( hello world )
```

Here's an example query which uses all these operators:

Example 5. Boolean query example

( cat -dog ) | ( cat -mouse)

There always is implicit AND operator, so "hello world" query actually means "hello & world".

OR operator precedence is higher than AND, so "looking for cat | dog | mouse" means "looking for ( cat | dog | mouse )" and not "(looking for cat) | dog | mouse".

Queries like "-dog", which implicitly include all documents from the collection, can not be evaluated. This is both for technical and performance reasons. Technically, Sphinx does not always keep a list of all IDs. Performance-wise, when the collection is huge (ie. 10-100M documents), evaluating such queries could take very long.

4.3. Extended query syntax

The following special operators and modifiers can be used when using the extended matching mode:

operator OR:
```
hello | world
```
operator NOT:
```
hello -world
hello !world
```
field search operator:
```
@title hello @body world
```
field position limit modifier (introduced in version 0.9.9-rc1):
```
@body[50] hello
```
multiple-field search operator:
```
@(title,body) hello world
```
all-field search operator:
```
@* hello
```
phrase search operator:
```
"hello world"
```
proximity search operator:
```
"hello world"~10
```
quorum matching operator:
```
"the world is a wonderful place"/3
```
strict order operator (aka operator "before"):
```
aaa << bbb << ccc
```
exact form modifier (introduced in version 0.9.9-rc1):
```
raining =cats and =dogs
```
field-start and field-end modifier (introduced in version 0.9.9-rc2):
```
^hello world$
```

Here's an example query that uses some of these operators:

Example 6. Extended matching mode: query example

"hello world" @title "example program"~5 @body python -(php|perl) @* code

The full meaning of this search is:

Find the words 'hello' and 'world' adjacently in any field in a document;
Additionally, the same document must also contain the words 'example' and 'program' in the title field, with up to, but not including, 10 words between the words in question; (E.g. "example PHP program" would be matched however "example script to introduce outside data into the correct context for your program" would not because two terms have 10 or more words between them)
Additionally, the same document must contain the word 'python' in the body field, but not contain either 'php' or 'perl';
Additionally, the same document must contain the word 'code' in any field.

There always is implicit AND operator, so "hello world" means that both "hello" and "world" must be present in matching document.

OR operator precedence is higher than AND, so "looking for cat | dog | mouse" means "looking for ( cat | dog | mouse )" and not "(looking for cat) | dog | mouse".

Field limit operator limits subsequent searching to a given field. Normally, query will fail with an error message if given field name does not exist in the searched index. However, that can be suppressed by specifying "@@relaxed" option at the very beginning of the query:

@@relaxed @nosuchfield my query

This can be helpful when searching through heterogeneous indexes with different schemas.

Field position limit, introduced in version 0.9.9-rc1, additionaly restricts the searching to first N position within given field (or fields). For example, "@body[50] hello" will not match the documents where the keyword 'hello' occurs at position 51 and below in the body.

Proximity distance is specified in words, adjusted for word count, and applies to all words within quotes. For instance, "cat dog mouse"~5 query means that there must be less than 8-word span which contains all 3 words, ie. "CAT aaa bbb ccc DOG eee fff MOUSE" document will not match this query, because this span is exactly 8 words long.

Quorum matching operator introduces a kind of fuzzy matching. It will only match those documents that pass a given threshold of given words. The example above ("the world is a wonderful place"/3) will match all documents that have at least 3 of the 6 specified words.

Strict order operator (aka operator "before"), introduced in version 0.9.9-rc2, will match the document only if its argument keywords occur in the document exactly in the query order. For instance, "black << cat" query (without quotes) will match the document "black and white cat" but not the "that cat was black" document. Order operator has the lowest priority. It can be applied both to just keywords and more complex expressions, ie. this is a valid query:

(bag of words) << "exact phrase" << red|green|blue

Exact form keyword modifier, introduced in version 0.9.9-rc1, will match the document only if the keyword occurred in exactly the specified form. The default behaviour is to match the document if the stemmed keyword matches. For instance, "runs" query will match both the document that contains "runs" and the document that contains "running", because both forms stem to just "run" - while "=runs" query will only match the first document. Exact form operator requires index_exact_words option to be enabled. This is a modifier that affects the keyword and thus can be used within operators such as phrase, proximity, and quorum operators.

Field-start and field-end keyword modifiers, introduced in version 0.9.9-rc2, will make the keyword match only if it occurred at the very start or the very end of a fulltext field, respectively. For instance, the query "^hello world$" (with quotes and thus combining phrase operator and start/end modifiers) will only match documents that contain at least one field that has exactly these two keywords.

Starting with 0.9.9-rc1, arbitrarily nested brackets and negations are allowed. However, the query must be possible to compute without involving an implicit list of all documents:

// correct query
aaa -(bbb -(ccc ddd))

// queries that are non-computable
-aaa
aaa | -bbb

4.4. Weighting

Specific weighting function (currently) depends on the search mode.

There are these major parts which are used in the weighting functions:

phrase rank,
statistical rank.

Phrase rank is based on a length of longest common subsequence (LCS) of search words between document body and query phrase. So if there's a perfect phrase match in some document then its phrase rank would be the highest possible, and equal to query words count.

Statistical rank is based on classic BM25 function which only takes word frequencies into account. If the word is rare in the whole database (ie. low frequency over document collection) or mentioned a lot in specific document (ie. high frequency over matching document), it receives more weight. Final BM25 weight is a floating point number between 0 and 1.

In all modes, per-field weighted phrase ranks are computed as a product of LCS multiplied by per-field weight speficifed by user. Per-field weights are integer, default to 1, and can not be set lower than 1.

In SPH_MATCH_BOOLEAN mode, no weighting is performed at all, every match weight is set to 1.

In SPH_MATCH_ALL and SPH_MATCH_PHRASE modes, final weight is a sum of weighted phrase ranks.

In SPH_MATCH_ANY mode, the idea is essentially the same, but it also adds a count of matching words in each field. Before that, weighted phrase ranks are additionally mutliplied by a value big enough to guarantee that higher phrase rank in any field will make the match ranked higher, even if it's field weight is low.

In SPH_MATCH_EXTENDED mode, final weight is a sum of weighted phrase ranks and BM25 weight, multiplied by 1000 and rounded to integer.

This is going to be changed, so that MATCH_ALL and MATCH_ANY modes use BM25 weights as well. This would improve search results in those match spans where phrase ranks are equal; this is especially useful for 1-word queries.

The key idea (in all modes, besides boolean) is that better subphrase matches are ranked higher, and perfect matches are pulled to the top. Author's experience is that this phrase proximity based ranking provides noticeably better search quality than any statistical scheme alone (such as BM25, which is commonly used in other search engines).

4.5. Sorting modes

There are the following result sorting modes available:

SPH_SORT_RELEVANCE mode, that sorts by relevance in descending order (best matches first);
SPH_SORT_ATTR_DESC mode, that sorts by an attribute in descending order (bigger attribute values first);
SPH_SORT_ATTR_ASC mode, that sorts by an attribute in ascending order (smaller attribute values first);
SPH_SORT_TIME_SEGMENTS mode, that sorts by time segments (last hour/day/week/month) in descending order, and then by relevance in descending order;
SPH_SORT_EXTENDED mode, that sorts by SQL-like combination of columns in ASC/DESC order;
SPH_SORT_EXPR mode, that sorts by an arithmetic expression.

SPH_SORT_RELEVANCE ignores any additional parameters and always sorts matches by relevance rank. All other modes require an additional sorting clause, with the syntax depending on specific mode. SPH_SORT_ATTR_ASC, SPH_SORT_ATTR_DESC and SPH_SORT_TIME_SEGMENTS modes require simply an attribute name. SPH_SORT_RELEVANCE is equivalent to sorting by "@weight DESC, @id ASC" in extended sorting mode, SPH_SORT_ATTR_ASC is equivalent to "attribute ASC, @weight DESC, @id ASC", and SPH_SORT_ATTR_DESC to "attribute DESC, @weight DESC, @id ASC" respectively.

SPH_SORT_TIME_SEGMENTS mode

In SPH_SORT_TIME_SEGMENTS mode, attribute values are split into so-called time segments, and then sorted by time segment first, and by relevance second.

The segments are calculated according to the current timestamp at the time when the search is performed, so the results would change over time. The segments are as follows:

last hour,
last day,
last week,
last month,
last 3 months,
everything else.

These segments are hardcoded, but it is trivial to change them if necessary.

This mode was added to support searching through blogs, news headlines, etc. When using time segments, recent records would be ranked higher because of segment, but withing the same segment, more relevant records would be ranked higher - unlike sorting by just the timestamp attribute, which would not take relevance into account at all.

SPH_SORT_EXTENDED mode

In SPH_SORT_EXTENDED mode, you can specify an SQL-like sort expression with up to 5 attributes (including internal attributes), eg:

@relevance DESC, price ASC, @id DESC

Both internal attributes (that are computed by the engine on the fly) and user attributes that were configured for this index are allowed. Internal attribute names must start with magic @-symbol; user attribute names can be used as is. In the example above, @relevance and @id are internal attributes and price is user-specified.

Known internal attributes are:

@id (match ID)
@weight (match weight)
@rank (match weight)
@relevance (match weight)
@random (return results in random order)

@rank and @relevance are just additional aliases to @weight.

SPH_SORT_EXPR mode

Expression sorting mode lets you sort the matches by an arbitrary arithmetic expression, involving attribute values, internal attributes (@id and @weight), arithmetic operations, and a number of built-in functions. Here's an example:

$cl->SetSortMode ( SPH_SORT_EXPR,
	"@weight + ( user_karma + ln(pageviews) )*0.1" );

The following operators and functions are supported. They are mimiced after MySQL. The functions take a number of arguments depending on the specific function.

Operators: +, -, *, /, <, > <=, >=, =, <>.
Boolean operators: AND, OR, NOT.
0-argument functions: NOW().
Unary (1-argument) functions: ABS(), CEIL(), FLOOR(), SIN(), COS(), LN(), LOG2(), LOG10(), EXP(), SQRT(), BIGINT().
Binary (2-argument) functions: MIN(), MAX(), POW(), IDIV().
Other functions: IF(), INTERVAL(), IN(), GEODIST().

Calculations can be performed in three different modes: (a) using single-precision, 32-bit IEEE 754 floating point values (the default), (b) using signed 32-bit integers, (c) using 64-bit signed integers. The expression parser will automatically switch to integer mode if there are no operations the result in a floating point value. Otherwise, it will use the default floating point mode. For instance, "a+b" will be computed using 32-bit integers if both arguments are 32-bit integers; or using 64-bit integers if both arguments are integers but one of them is 64-bit; or in floats otherwise. However, "a/b" or "sqrt(a)" will always be computed in floats, because these operations return non-integer result. To avoid the first, you can use IDIV(). Also, "a*b" will not be automatically promoted to 64-bit when the arguments are 32-bit. To enforce 64-bit results, you can use BIGINT(). (But note that if there are non-integer operations, BIGINT() will simply be ignored.)

Comparison operators (eg. = or <=) return 1.0 when the condition is true and 0.0 otherwise. For instance, (a=b)+3 will evaluate to 4 when attribute 'a' is equal to attribute 'b', and to 3 when 'a' is not. Unlike MySQL, the equality comparisons (ie. = and <> operators) introduce a small equality threshold (1e-6 by default). If the difference between compared values is within the threshold, they will be considered equal.

Boolean operators (AND, OR, NOT) were introduced in 0.9.9-rc2 and behave as usual. They are left-associative and have the least priority compared to other operators. NOT has more priority than AND and OR but nevertheless less than any other operator. AND and OR have the same priority so brackets use is recommended to avoid confusion in complex expressions.

All unary and binary functions are straightforward, they behave just like their mathematical counterparts. But IF() behavior needs to be explained in more detail. It takes 3 arguments, check whether the 1st argument is equal to 0.0, returns the 2nd argument if it is not zero, or the 3rd one when it is. Note that unlike comparison operators, IF() does not use a threshold! Therefore, it's safe to use comparison results as its 1st argument, but arithmetic operators might produce unexpected results. For instance, the following two calls will produce different results even though they are logically equivalent:

IF ( sqrt(3)*sqrt(3)-3<>0, a, b )
IF ( sqrt(3)*sqrt(3)-3, a, b )

In the first case, the comparison operator <> will return 0.0 (false) because of a threshold, and IF() will always return 'b' as a result. In the second one, the same sqrt(3)*sqrt(3)-3 expression will be compared with zero without threshold by the IF() function itself. But its value will be slightly different from zero because of limited floating point calculations precision. Because of that, the comparison with 0.0 done by IF() will not pass, and the second variant will return 'a' as a result.

BIGINT() function, introduced in version 0.9.9-rc1, forcibly promotes the integer argument to 64-bit type, and does nothing on floating point argument. It's intended to help enforce evaluation of certain expressions (such as "a*b") in 64-bit mode even though all the arguments are 32-bit.

IDIV() functions performs an integer division on its 2 arguments. The result is integer as well, unlike "a/b" result.

IN(expr,val1,val2,...), introduced in version 0.9.9-rc1, takes 2 or more arguments, and returns 1 if 1st argument (expr) is equal to any of the other arguments (val1..valN), or 0 otherwise. Currently, all the checked values (but not the expression itself!) are required to be constant. (Its technically possible to implement arbitrary expressions too, and that might be implemented in the future.) Constants are pre-sorted and then binary search is used, so IN() even against a big arbitrary list of constants will be very quick. Starting with 0.9.9-rc2, first argument can also be a MVA attribute. In that case, IN() will return 1 if any of the MVA values is equal to any of the other arguments.

INTERVAL(expr,point1,point2,point3,...), introduced in version 0.9.9-rc1, takes 2 or more arguments, and returns the index of the argument that is less than the first argument: it returns 0 if expr<point1, 1 if point1<=expr<point2, and so on. It is required that point1<point2<...<pointN for this function to work correctly.

NOW(), introduced in version 0.9.9-rc1, is a helper function that returns current timestamp as a 32-bit integer.

GEODIST(lat1,long1,lat2,long2) function, introduced in version 0.9.9-rc2, computes geosphere distance between two given points specified by their coordinates. Note that both latitudes and longitudes must be in radians and the result will be in meters. You can use arbitrary expression as any of the four coordinates. An optimized path will be selected when one pair of the arguments refers directly to a pair attributes and the other one is constant.

4.6. Grouping (clustering) search results

Sometimes it could be useful to group (or in other terms, cluster) search results and/or count per-group match counts - for instance, to draw a nice graph of how much maching blog posts were there per each month; or to group Web search results by site; or to group matching forum posts by author; etc.

In theory, this could be performed by doing only the full-text search in Sphinx and then using found IDs to group on SQL server side. However, in practice doing this with a big result set (10K-10M matches) would typically kill performance.

To avoid that, Sphinx offers so-called grouping mode. It is enabled with SetGroupBy() API call. When grouping, all matches are assigned to different groups based on group-by value. This value is computed from specified attribute using one of the following built-in functions:

SPH_GROUPBY_DAY, extracts year, month and day in YYYYMMDD format from timestamp;
SPH_GROUPBY_WEEK, extracts year and first day of the week number (counting from year start) in YYYYNNN format from timestamp;
SPH_GROUPBY_MONTH, extracts month in YYYYMM format from timestamp;
SPH_GROUPBY_YEAR, extracts year in YYYY format from timestamp;
SPH_GROUPBY_ATTR, uses attribute value itself for grouping.

The final search result set then contains one best match per group. Grouping function value and per-group match count are returned along as "virtual" attributes named @group and @count respectively.

The result set is sorted by group-by sorting clause, with the syntax similar to SPH_SORT_EXTENDED sorting clause syntax. In addition to @id and @weight, group-by sorting clause may also include:

@group (groupby function value),
@count (amount of matches in group).

The default mode is to sort by groupby value in descending order, ie. by "@group desc".

On completion, total_found result parameter would contain total amount of matching groups over he whole index.

WARNING: grouping is done in fixed memory and thus its results are only approximate; so there might be more groups reported in total_found than actually present. @count might also be underestimated. To reduce inaccuracy, one should raise max_matches. If max_matches allows to store all found groups, results will be 100% correct.

For example, if sorting by relevance and grouping by "published" attribute with SPH_GROUPBY_DAY function, then the result set will contain

one most relevant match per each day when there were any matches published,
with day number and per-day match count attached,
sorted by day number in descending order (ie. recent days first).

Starting with version 0.9.9-rc2, aggregate functions (AVG(), MIN(), MAX(), SUM()) are supported through SetSelect() API call when using GROUP BY.

4.7. Distributed searching

To scale well, Sphinx has distributed searching capabilities. Distributed searching is useful to improve query latency (ie. search time) and throughput (ie. max queries/sec) in multi-server, multi-CPU or multi-core environments. This is essential for applications which need to search through huge amounts data (ie. billions of records and terabytes of text).

The key idea is to horizontally partition (HP) searched data accross search nodes and then process it in parallel.

Partitioning is done manually. You should

setup several instances of Sphinx programs (indexer and searchd) on different servers;
make the instances index (and search) different parts of data;
configure a special distributed index on some of the searchd instances;
and query this index.

This index only contains references to other local and remote indexes - so it could not be directly reindexed, and you should reindex those indexes which it references instead.

When searchd receives a query against distributed index, it does the following:

connects to configured remote agents;
issues the query;
sequentially searches configured local indexes (while the remote agents are searching);
retrieves remote agents' search results;
merges all the results together, removing the duplicates;
sends the merged resuls to client.

From the application's point of view, there are no differences between searching through a regular index, or a distributed index at all. That is, distributed indexes are fully transparent to the application, and actually there's no way to tell whether the index you queried was distributed or local. (Even though as of 0.9.9 Sphinx does not allow to combine searching through distributed indexes with anything else, this constraint will be lifted in the future.)

Any searchd instance could serve both as a master (which aggregates the results) and a slave (which only does local searching) at the same time. This has a number of uses:

every machine in a cluster could serve as a master which searches the whole cluster, and search requests could be balanced between masters to achieve a kind of HA (high availability) in case any of the nodes fails;
if running within a single multi-CPU or multi-core machine, there would be only 1 searchd instance quering itself as an agent and thus utilizing all CPUs/core.

It is scheduled to implement better HA support which would allow to specify which agents mirror each other, do health checks, keep track of alive agents, load-balance requests, etc.

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

This additional block is information regarding I/O operations in performing the search: the number of file I/O operations carried out, the amount of data in kilobytes read from the index files and time spent on I/O operations (although there is a background processing component, the bulk of this time is the I/O operation time)

4.9. MySQL protocol support and SphinxQL

Starting with version 0.9.9-rc2, Sphinx searchd daemon supports MySQL binary network protocol and can be accessed with regular MySQL API. For instance, 'mysql' CLI client program works well. Here's an example of querying Sphinx using MySQL client:

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

The new access method is supported in addition to native APIs which all still work perfectly well. In fact, both access methods can be used at the same time. Also, native API is still the default access method. MySQL protocol support needs to be additionally configured. This is a matter of 1-line config change, adding a new listener with mysql41 specified as a protocol:

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

indexer is the first of the two principle tools as part of Sphinx. Invoked from either the command line directly, or as part of a larger script, indexer is solely responsible for gathering the data that will be searchable.

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

Essentially you would list the different possible indexes (that you would later make available to search) in sphinx.conf, so when calling indexer, as a minimum you need to be telling it what index (or indexes) you want to index.

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

As part of the configuration file, sphinx.conf, you specify one or more indexes for your data. You might call indexer to reindex one of them, ad-hoc, or you can tell it to process all indexes - you are not limited to calling just one, or all at once, you can always pick some combination of the available indexes.

The majority of the options for indexer are given in the configuration file, however there are some options you might need to specify on the command line as well, as they can affect how the indexing operation is performed. These options are:

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Query() API call rather than JOIN.
WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN(), AND, NOT, and BETWEEN are all supported and map directly to filters. OR is not supported yet but will be in the future. MATCH('query') is supported and maps to fulltext query. Query will be interpreted according to full-text query language rules. There must be at most one MATCH() in the clause.
GROUP BY clause. Currently only supports grouping by a single column. The column however can be a computed expression:
```
SELECT *, group_id*1000+article_type AS gkey FROM example GROUP BY gkey
```
Aggregate functions (AVG(), MIN(), MAX(), SUM()) in column list clause are supported. Arguments to aggregate functions can be either plain attributes or arbitrary expressions. COUNT(*) is implicitly supported as using GROUP BY will add @count column to result set. Explicit support might be added in the future. COUNT(DISTINCT attr) is supported. Currently there can be at most one COUNT(DISTINCT) per query and an argument needs to be an attribute. Both current restrictions on COUNT(DISTINCT) might be lifted in the future.
```
SELECT *, AVG(price) AS avgprice, COUNT(DISTINCT storeid)
FROM products
WHERE MATCH('ipod')
GROUP BY vendorid
```

WITHIN GROUP ORDER BY clause. This is a Sphinx specific extension that lets you control how the best row within a group will to be selected. The syntax matches that of regular ORDER BY clause:

SELECT *, INTERVAL(posted,NOW()-7*86400,NOW()-86400) AS timeseg
FROM example WHERE MATCH('my search query')
GROUP BY siteid
WITHIN GROUP ORDER BY @weight DESC
ORDER BY timeseg DESC, @weight DESC

ORDER BY clause. Unlike in regular SQL, only column names (not expressions) are allowed and explicit ASC and DESC are required. The columns however can be computed expressions:
```
SELECT *, @weight*10+docboost AS skey FROM example ORDER BY skey
```
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike in regular SQL (but like in Sphinx API), an implicit LIMIT 0,20 is present by default.
OPTION clause. This is a Sphinx specific extension that lets you control a number of per-query options. The syntax is:
```
OPTION <optionname>=<value> [ , ... ]
```
Supported options and respectively allowed values are:
- 'ranker' - any of 'proximity_bm25', 'bm25', 'none', 'wordcount', 'proximity', 'matchany', or 'fieldmask'
- 'max_matches' - integer (per-query max matches value)
- 'cutoff' - integer (max found matches threshold)
- 'max_query_time' - integer (max search time threshold, msec)
- 'retry_count' - integer (distributed retries count)
- 'retry_delay' - integer (distributed retry delay, msec)
Example:
```
SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000
```

SHOW WARNINGS statement can be used to retrieve the warning produced by the latest query. The error message will be returned along with the query itself:

mysql> SELECT * FROM test1 WHERE MATCH('@@title hello') \G
ERROR 1064 (42000): index test1: syntax error, unexpected TOK_FIELDLIMIT
near '@title hello'

mysql> SELECT * FROM test1 WHERE MATCH('@title -hello') \G
ERROR 1064 (42000): index test1: query is non-computable (single NOT operator)

mysql> SELECT * FROM test1 WHERE MATCH('"test doc"/3') \G
*************************** 1. row ***************************
        id: 4
    weight: 2500
  group_id: 2
date_added: 1231721236
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: warning
   Code: 1000
Message: quorum threshold too high (words=2, thresh=3); replacing quorum operator
         with AND operator
1 row in set (0.00 sec)

SHOW STATUS shows a number of useful performance counters. IO and CPU counters will only be available if searchd was started with --iostats and --cpustats switches respectively.

mysql> SHOW STATUS;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| uptime             | 216   |
| connections        | 3     |
| maxed_out          | 0     |
| command_search     | 0     |
| command_excerpt    | 0     |
| command_update     | 0     |
| command_keywords   | 0     |
| command_persist    | 0     |
| command_status     | 0     |
| agent_connect      | 0     |
| agent_retry        | 0     |
| queries            | 10    |
| dist_queries       | 0     |
| query_wall         | 0.075 |
| query_cpu          | OFF   |
| dist_wall          | 0.000 |
| dist_local         | 0.000 |
| dist_wait          | 0.000 |
| query_reads        | OFF   |
| query_readkb       | OFF   |
| query_readtime     | OFF   |
| avg_query_wall     | 0.007 |
| avg_query_cpu      | OFF   |
| avg_dist_wall      | 0.000 |
| avg_dist_local     | 0.000 |
| avg_dist_wait      | 0.000 |
| avg_query_reads    | OFF   |
| avg_query_readkb   | OFF   |
| avg_query_readtime | OFF   |
+--------------------+-------+
29 rows in set (0.00 sec)

SHOW META shows additional meta-information about the latest query such as query time and keyword statistics:

mysql> SELECT * FROM test1 WHERE MATCH('test|one|two');
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    1 |   3563 |      456 | 1231721236 |
|    2 |   2563 |      123 | 1231721236 |
|    4 |   1480 |        2 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.01 sec)

mysql> SHOW META;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 3     |
| total_found   | 3     |
| time          | 0.005 |
| keyword[0]    | test  |
| docs[0]       | 3     |
| hits[0]       | 5     |
| keyword[1]    | one   |
| docs[1]       | 1     |
| hits[1]       | 2     |
| keyword[2]    | two   |
| docs[2]       | 1     |
| hits[2]       | 2     |
+---------------+-------+
12 rows in set (0.00 sec)

5. Command line tools reference

As mentioned elsewhere, Sphinx is not a single program called 'sphinx', but a collection of 4 separate programs which collectively form Sphinx. This section covers these tools and how to use them.

5.1. `indexer` command reference

The calling syntax for indexer is as follows:

indexer [OPTIONS] [indexname1 [indexname2 [...]]]

If sphinx.conf contained details on 2 indexes, mybigindex and mysmallindex, you could do the following:

$ indexer mybigindex
$ indexer mysmallindex mybigindex

--config <file> (-c <file> for short) tells indexer to use the given file as its configuration. Normally, it will look for sphinx.conf in the installation directory (e.g. /usr/local/sphinx/etc/sphinx.conf if installed into /usr/local/sphinx), followed by the current directory you are in when calling indexer from the shell. This is most of use in shared environments where the binary files are installed somewhere like /usr/local/sphinx/ but you want to provide users with the ability to make their own custom Sphinx set-ups, or if you want to run multiple instances on a single server. In cases like those you could allow them to create their own sphinx.conf files and pass them to indexer with this option. For example:
```
$ indexer --config /home/myuser/sphinx.conf myindex
```
--all tells indexer to update every index listed in sphinx.conf, instead of listing individual indexes. This would be useful in small configurations, or cron-type or maintenance jobs where the entire index set will get rebuilt each day, or week, or whatever period is best. Example usage:
```
$ indexer --config /home/myuser/sphinx.conf --all
```
--rotate is used for rotating indexes. Unless you have the situation where you can take the search function offline without troubling users, you will almost certainly need to keep search running whilst indexing new documents. --rotate creates a second index, parallel to the first (in the same place, simply including .new in the filenames). Osist p>

4.8. `searchd` query log format

searchd logs all succesfully executed search queries into query log file. Here's an example:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj] test
[Fri Jun 29 21:20:34 2007] 0.024 sec [all/0/rel 19886 (0,20) @channel_id] [lj] test

This log format is as follows:

[query-date] query-time [match-mode/filters-count/sort-mode
    total-matches (offset,limit) @groupby-attr] [index-name] query

Match mode can take one of the following values:

"all" for SPH_MATCH_ALL mode;
"any" for SPH_MATCH_ANY mode;
"phr" for SPH_MATCH_PHRASE mode;
"bool" for SPH_MATCH_BOOLEAN mode;
"ext" for SPH_MATCH_EXTENDED mode;
"ext2" for SPH_MATCH_EXTENDED2 mode;
"scan" if the full scan mode was used, either by being specified with SPH_MATCH_FULLSCAN, or if the query was empty (as documented under Matching Modes)

Sort mode can take one of the following values:

"rel" for SPH_SORT_RELEVANCE mode;
"attr-" for SPH_SORT_ATTR_DESC mode;
"attr+" for SPH_SORT_ATTR_ASC mode;
"tsegs" for SPH_SORT_TIME_SEGMENTS mode;
"ext" for SPH_SORT_EXTENDED mode.

Additionally, if searchd was started with --iostats, there will be a block of data after where the index(es) searched are listed.

A query log entry might take the form of:

[Fri Jun 29 21:17:58 2007] 0.004 sec [all/0/rel 35254 (0,20)] [lj]
   [ios=6 kb=111.1 ms=0.5] test

4.9. MySQL protocol support and SphinxQL

$ mysql -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test1 WHERE MATCH('test') 
    -> ORDER BY group_id ASC OPTION ranker=bm25;
+------+--------+----------+------------+
| id   | weight | group_id | date_added |
+------+--------+----------+------------+
|    4 |   1442 |        2 | 1231721236 |
|    2 |   2421 |      123 | 1231721236 |
|    1 |   2421 |      456 | 1231721236 |
+------+--------+----------+------------+
3 rows in set (0.00 sec)

Note that mysqld was not even running on the test machine. Everything was handled by searchd itself.

listen = localhost:9306:mysql41

Just supporting the protocol and not the SQL syntax would be useless so Sphinx now also supports a tiny subset of SQL that we dubbed SphinxQL. Currently implemented statements are:

SELECT
SHOW WARNINGS
SHOW STATUS
SHOW META

SELECT syntax is based upon regular SQL but adds several Sphinx-specific extensions and has a few omissions (such as (currently) missing support for JOINs). Specifically,

Column list clause. Column names, arbitrary expressions, and star ('*') are all allowed (ie. "SELECT @id, group_id*123+456 FROM test1" will work). Unlike in regular SQL, all computed expressions must be aliased with a valid identifier. Special names such as @id and @weight should currently be used with leading at-sign. This at-sign requirement will be lifted in the future.
FROM clause. FROM clause should contain the list of indexes to search through. Unlike in regular SQL, comma means enumeration of full-text indexes as in Qu

Sphinx 0.9.9-rc2 reference manual

Free open-source SQL full-text search engine

1. Introduction

1.1. About

1.2. Sphinx features

1.3. Where to get Sphinx

1.4. License

1.5. Author and contributors

Author

Contributors

1.6. History

2. Installation

2.1. Supported systems

2.2. Required tools

2.3. Installing Sphinx on Linux

2.4. Installing Sphinx on Windows

2.5. Known installation issues

2.6. Quick Sphinx usage tour

3. Indexing

3.1. Data sources

3.2. Attributes

Example sphinx.conf part:

Example application code (in PHP):

3.3. MVA (multi-valued attributes)

3.4. Indexes

3.5. Restrictions on the source data

3.6. Charsets, case folding, and translation tables

3.7. SQL data sources (MySQL, PostgreSQL)

Ranged queries

sql_post vs. sql_post_index

3.8. xmlpipe data source

3.9. xmlpipe2 data source

3.10. Live index updates

3.11. Index merging

4. Searching

4.1. Matching modes

4.2. Boolean query syntax

4.3. Extended query syntax

4.4. Weighting

4.5. Sorting modes

SPH_SORT_TIME_SEGMENTS mode

SPH_SORT_EXTENDED mode

SPH_SORT_EXPR mode

4.6. Grouping (clustering) search results

4.7. Distributed searching

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

5.1. indexer command reference

4.8. searchd query log format

4.9. MySQL protocol support and SphinxQL

5. Command line tools reference

`sql_post` vs. `sql_post_index`

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format

5.1. `indexer` command reference

4.8. `searchd` query log format