Sphinx and MySQL. Installing and Using the SphinxSE Engine · English

Continuing the topic of Sphinx, I’ll talk about SphinxSE, an engine for MySQL that lets you work with Sphinx indexes directly from MySQL.

What Is SphinxSE?

The SphinxSE engine does not store data itself; instead, it acts as a bridge that lets the MySQL server communicate with “searchd”, run search queries, and retrieve search results. All indexing and searching takes place outside MySQL.

SphinxSE can only be built for MySQL 5.x, using the plugin architecture, which is not supported in MySQL 4.x.

SphinxSE lets you:

Easily port MySQL FTS (full-text search) applications to Sphinx;
Use Sphinx where using an API is not possible, for example, if no API is available for a particular language;
Optimize the processing of results returned by Sphinx on the MySQL side, for example: JOIN search results with other MySQL tables, add extra data filtering on the MySQL side, etc.

Installing SphinxSE

We’ll install from source on FreeBSD 6.3.

First, let’s download the distributions:


# fetch http://www.sphinxsearch.com/downloads/sphinx-0.9.8.1.tar.gz
# fetch http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.34.tar.gz

Download MySQL: http://downloads.mysql.com/archives.php?p=mysql-5.1&o=other Download Sphinx: http://www.sphinxsearch.com/downloads.html

Note that the Sphinx download list includes “MySQL 5.0.45 with SphinxSE 0.9.8”, and the link “mysql-5.0.45-sphinxse-0.9.8-win32.zip” indicates that it is for Win32 only.

Now, let’s unpack the archives:


# tar -xzf mysql-5.1.34.tar.gz
# tar -xzf sphinx-0.9.8.1.tar.gz

Let’s copy the SphinxSE source into the MySQL directory we unpacked:


# cp -R sphinx-0.9.8.1/mysqlse mysql-5.1.34/storage/sphinx

Run “autorun.sh” to rebuild the source:


# cd mysql-5.1.34
# sh BUILD/autorun.sh

Let’s install the additional engines, including Sphinx:


# ./configure --with-plugins=sphinx,innobase,archive,blackhole
# make && make install

Start MySQL:


# /usr/local/etc/rc.d/mysql-server start

Let’s enter the MySQL console and see which engines we have:


mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| SPHINX     | YES     | Sphinx storage engine 0.9.8                                    | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

We’ve installed the engine; now let’s try using it!

Using SphinxSE

For this example, we’ll continue working with the “adw0rd_wp” index that we created in the previous article about Sphinx.

First, let’s create a table:


CREATE TABLE `test_sphinx`
(
    `id`          INTEGER NOT NULL,
    `weight`      INTEGER NOT NULL,
    `query`       VARCHAR(3072) NOT NULL,
    `group_id`    INTEGER,
    INDEX(`query`)
) ENGINE=SPHINX CONNECTION="sphinx://localhost:3312/adw0rd_wp";

Now let’s test a query against the “adw0rd_wp” index:


mysql> SELECT * FROM `test_sphinx` WHERE `query` = 'sphinx';
+------+--------+--------+----------+
| id   | weight | query  | group_id |
+------+--------+--------+----------+
| 3366 |      2 | sphinx |        0 |
| 3341 |      1 | sphinx |        0 |
| 3368 |      1 | sphinx |        0 |
+------+--------+--------+----------+
3 rows in set (0.12 sec)

Here, in “query”, you can enter the phrase to search for, as well as additional search parameters, such as “mode” or “sort”, or specify indexes:


... WHERE query='test;mode=any';
... WHERE query='test;sort=attr_asc:group_id';
... WHERE query='test;index=test1,test2,test3;';

The results are comparable to the previous search through the API: http://demo.adw0rd.ru/sphinxsearch/?s=sphinx. More details on using SphinxSE are here: http://www.sphinxsearch.com/docs/current.html#sphinxse-using