Databases Reference
In-Depth Information
Although you can achieve this by sending the result's document IDs to MySQL in a
query, that strategy leads to neither the cleanest nor the fastest code. For high-volume
situations, you should consider using SphinxSE, a pluggable storage engine that you
can compile into MySQL 5.0 or newer, or load into MySQL 5.1 or newer as a plugin.
SphinxSE lets programmers query
searchd
and access search results from within
MySQL. The usage is as simple as creating a special table with an
ENGINE=SPHINX
clause
(and an optional
CONNECTION
clause to locate the Sphinx server if it's at a nondefault
location), and then running queries against that table:
mysql>
CREATE TABLE search_table (
->
id INTEGER NOT NULL,
->
weight INTEGER NOT NULL,
->
query VARCHAR(3072) NOT NULL,
->
group_id INTEGER,
->
INDEX(query)
->
) ENGINE=SPHINX CONNECTION="sphinx://localhost:3312/test";
Query OK, 0 rows affected (0.12 sec)
mysql>
SELECT * FROM search_table WHERE query='test;mode=all' \G
*************************** 1. row ***************************
id: 123
weight: 1
query: test;mode=all
group_id: 45
1 row in set (0.00 sec)
Each
SELECT
passes a Sphinx query as the
query
column in the
WHERE
clause. The Sphinx
searchd
server returns the results. The SphinxSE storage engine then translates these
into MySQL results and returns them to the
SELECT
statement.
Queries might include
JOIN
s with any other tables stored using any other storage
engines.
The SphinxSE engine supports most searching options available via the API, too. You
can specify options such as filtering and limits by plugging additional clauses into the
query string:
mysql>
SELECT * FROM search_table WHERE query='test;mode=all;
->
filter=group_id,5,7,11;maxmatches=3000';
Per-query and per-word statistics that are returned by the API are also accessible
through
SHOW STATUS
:
mysql>
SHOW ENGINE SPHINX STATUS \G
*************************** 1. row ***************************
Type: SPH INX
Name: stats
Status: total: 3, total found: 3, time: 8, words: 1
*************************** 2. row ***************************
Type: SPHINX
Name: words
Status: test:3:5
2 rows in set (0.00 sec)
