Basic Usage
To exploration via SphinxSE, you would need to create a superior ENGINE=SPHINX "search table", and then SELECT from it with full text request put into the WHERE section for query column.
Here is a sample create declaration and search query:
The first three columns of the search table must take a type of BIGINT for the 1st column (document id), INTEGER or BIGINT for the 2nd column (match weight), and VARCHAR or TEXT for the 3rd column (your request), correspondingly. This mapping is static; you cannot ignore any of these three compulsory columns, or move them everywhere, or change types. Also, the query column must be indexed; all the others must be kept back unindexed. Column names are unnoticed so you can use random ones.
Supplementary columns must be either INTEGER, TIMESTAMP, BIGINT, VARCHAR, or FLOAT. They will be guaranteed to the attributes as long as in the Sphinx result set by name, so their names must match the attribute names detailed in sphinx.conf. If there's no such attribute name in the Sphinx search outcomes, the additional columns will have NULL values.
Special "virtual" attribute names can also be certain to SphinxSE columns. _sph_ needs to be used as a substitute of @ for that. For example, to obtain the values of '@groupby', '@count', or '@distinct' virtual attributes, use '_sph_groupby', '_sph_count' or '_sph_distinct' column names, correspondingly.
The CONNECTION string parameter is used to specify the default searchd host, port, and indexes for queries issued using this table. If no connection string is specified in CREATE
TABLE, index name '*' (ie. exploration all indexes) and '127.0.0.1:9312' are supposed. The connection string syntax is as follows:
You can change the default connection string later like so:
You can also prevail all these parameters per-query.
Note: To use Linux sockets you can adapt the searchd section of the Sphinx configuration file, setting the listen parameter to a socket file. Instruct SphinxSE about the socket using CONNECTION="unix:unix/domain/socket[:index]".
Search Choices
As seen in the sample above, both query text and search selections should be put into the 'WHERE' clause of the search query column (i.e. the 3rd column); the options are separated by semicolons (';') and separate names from values using an equals sign ('='). Any number of choices can be stated. Offered options are:
query - query text;
mode - matching mode. Must be one of "all", "any", "phrase", "boolean", or "extended". Default is "all";
sort - match sorting mode. Must be one of "relevance", "attr_desc", "attr_asc", "time_segments", or "extended". In all modes besides "relevance" attribute name (or sorting clause for "extended") is also required after a colon:
offset - offset into result set, default is 0;
limit - amount of matches to retrieve from result set, default is 20;
index - names of the indexes to search:
minid, maxid - min and max document ID to match;
weights - comma-separated list of weights to be assigned to Sphinx full-text fields:
filter, !filter - comma-separated attribute name and a set of values to match:
range, !range - comma-separated attribute name, min and max value to match:
maxmatches - per-query max matches value:
groupby - group-by function and attribute:
groupsort - group-by sorting clause:
indexweights - comma-separated list of index names and weights to use when searching through several indexes:
comment - a string to mark this query in query log (mapping to $comment parameter in Query() API call):
select - a string with expressions to compute (mapping to SetSelect() API call):
Note: It is much more efficient to allow Sphinx to perform sorting, filtering, and slicing of the result set than to raise max matches count and use 'WHERE', 'ORDER BY', and 'LIMIT' clauses on the MySQL side. This is for two reasons:
Sphinx does a number of optimizations and performs better than MySQL on these tasks.
Less data would need to be packed by searchd and transferred and unpacked by SphinxSE.
SHOW ENGINE SPHINX STATUS
Starting with version 0.9.9-rc1, additional query info besides the result set can be retrieved with the 'SHOW ENGINE SPHINX STATUS' statement:
This info can also be retrieved through status variables. Note that this method does not need super-user rights.
JOINs with SphinxSE
You can do JOINs on a SphinxSE search table and tables by means of other engines. Here's an sample with "documents" from example.sql:
RT Indexes Sphinx
First, RT indexes should be acknowledged in sphinx.conf, just as the other index type, but there are a couple important alterations to remember:
data sources are not essential and overlooked
you should clearly enumerate all the text fields, not objective attributes.
Here’s a sample: