▪️ Extensions

SphinxSE

SphinxSE is a MySQL storage engine which can be compiled into MySQL/MariaDB server using its pluggable architecture.

Despite the name, SphinxSE does not actually store any data itself. It is actually a built-in client which allows MySQL server to talk to searchd, run search queries, and obtain search results. All indexing and searching happen outside MySQL.

Obvious SphinxSE applications include:

  • easier porting of MySQL FTS applications to Manticore;
  • allowing Manticore use with programming languages for which native APIs are not available yet;
  • optimizations when additional Manticore result set processing on MySQL side is required (eg. JOINs with original document tables, additional MySQL-side filtering, etc).

Installing SphinxSE

You will need to obtain a copy of MySQL sources, prepare those, and then recompile MySQL binary. MySQL sources (mysql-5.x.yy.tar.gz) could be obtained from http://dev.mysql.com Web site.

Compiling MySQL 5.0.x with SphinxSE

  1. copy sphinx.5.0.yy.diff patch file into MySQL sources directory and run

    1. $ patch -p1 < sphinx.5.0.yy.diff

    If there’s no .diff file exactly for the specific version you need to: build, try applying .diff with closest version numbers. It is important that the patch should apply with no rejects.

  2. in MySQL sources directory, run

    1. $ sh BUILD/autorun.sh

  3. in MySQL sources directory, create sql/sphinx directory in and copy all files in mysqlse directory from Manticore sources there. Example:

    1. $ cp -R /root/builds/sphinx-0.9.7/mysqlse /root/builds/mysql-5.0.24/sql/sphinx

  4. configure MySQL and enable the new engine:

    1. $ ./configure --with-sphinx-storage-engine

  5. build and install MySQL:

    1. $ make
    2. $ make install

Compiling MySQL 5.1.x with SphinxSE

  1. in MySQL sources directory, create storage/sphinx directory in and copy all files in mysqlse directory from Manticore sources there. Example:

    1. $ cp -R /root/builds/sphinx-0.9.7/mysqlse /root/builds/mysql-5.1.14/storage/sphinx

  2. in MySQL sources directory, run

    1. $ sh BUILD/autorun.sh

  3. configure MySQL and enable Manticore engine:

    1. $ ./configure --with-plugins=sphinx

  4. build and install MySQL:

    1. $ make
    2. $ make install

Checking SphinxSE installation

To check whether SphinxSE has been successfully compiled into MySQL, launch newly built servers, run mysql client and issue SHOW ENGINES query. You should see a list of all available engines. Manticore should be present and “Support” column should contain “YES”:

  • sql

sql

  1. mysql> show engines;

Response

  1. +------------+----------+-------------------------------------------------------------+
  2. | Engine     | Support  | Comment                                                     |
  3. +------------+----------+-------------------------------------------------------------+
  4. | MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance      |
  5.   ...
  6. | SPHINX     | YES      | Manticore storage engine                                       |
  7.   ...
  8. +------------+----------+-------------------------------------------------------------+
  9. 13 rows in set (0.00 sec)

Using SphinxSE

To search via SphinxSE, you would need to create special ENGINE=SPHINX “search table”, and then SELECT from it with full text query put into WHERE clause for query column.

Let’s begin with an example create statement and search query:

  1. CREATE TABLE t1
  2. (
  3.     id          INTEGER UNSIGNED NOT NULL,
  4.     weight      INTEGER NOT NULL,
  5.     query       VARCHAR(3072) NOT NULL,
  6.     group_id    INTEGER,
  7.     INDEX(query)
  8. ) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test";
  10. SELECT * FROM t1 WHERE query='test it;mode=any';

First 3 columns of search table must have a types of INTEGER UNSINGED or 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 query), respectively. This mapping is fixed; you can not omit any of these three required columns, or move them around, or change types. Also, query column must be indexed; all the others must be kept unindexed. Column names are ignored so you can use arbitrary ones.

Additional columns must be either INTEGER, TIMESTAMP, BIGINT, VARCHAR, or FLOAT. They will be bound to attributes provided in Manticore result set by name, so their names must match attribute names specified in sphinx.conf. If there’s no such attribute name in Manticore search results, column will have NULL values.

Special “virtual” attributes names can also be bound to SphinxSE columns. _sph_ needs to be used instead of @ for that. For instance, to obtain the values of @groupby, @count, or @distinct virtualattributes, use _sph_groupby, _sph_count or _sph_distinct column names, respectively.

CONNECTION string parameter is to be used to specify Manticore host, port and table. If no connection string is specified in CREATE TABLE, table name * (i.e. search all tables) and localhost:9312 are assumed. The connection string syntax is as follows:

  1. CONNECTION="sphinx://HOST:PORT/TABLENAME"

You can change the default connection string later:

  1. mysql> ALTER TABLE t1 CONNECTION="sphinx://NEWHOST:NEWPORT/NEWTABLENAME";

You can also override all these parameters per-query.

As seen in example, both query text and search options should be put into WHERE clause on search query column (ie. 3rd column); the options are separated by semicolons; and their names from values by equality sign. Any number of options can be specified. Available 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:

    1. ... WHERE query='test;sort=attr_asc:group_id';
    2. ... WHERE query='test;sort=extended:@weight desc, group_id asc';

  • offset - offset into result set, default is 0;

  • limit - amount of matches to retrieve from result set, default is 20;

  • index - names of the tables to search:

    1. ... WHERE query='test;index=test1;';
    2. ... WHERE query='test;index=test1,test2,test3;';

  • minid, maxid - min and max document ID to match;

  • weights - comma-separated list of weights to be assigned to Manticore full-text fields:

    1. ... WHERE query='test;weights=1,2,3;';

  • filter, !filter - comma-separated attribute name and a set of values to match:

    1. # only include groups 1, 5 and 19
    2. ... WHERE query='test;filter=group_id,1,5,19;';
    3. # exclude groups 3 and 11
    4. ... WHERE query='test;!filter=group_id,3,11;';

  • range, !range - comma-separated (integer or bigint) Manticore attribute name, and min and max values to match:

    1. # include groups from 3 to 7, inclusive
    2. ... WHERE query='test;range=group_id,3,7;';
    3. # exclude groups from 5 to 25
    4. ... WHERE query='test;!range=group_id,5,25;';

  • floatrange, !floatrange - comma-separated (floating point) Manticore attribute name, and min and max values to match:

    1. # filter by a float size
    2. ... WHERE query='test;floatrange=size,2,3;';
    3. # pick all results within 1000 meter from geoanchor
    4. ... WHERE query='test;floatrange=@geodist,0,1000;';

  • maxmatches - per-query max matches value, as in max_matches search option:

    1. ... WHERE query='test;maxmatches=2000;';

  • cutoff - maximum allowed matches, as in cutoff search option:

    1. ... WHERE query='test;cutoff=10000;';

  • maxquerytime - maximum allowed query time (in milliseconds), as in max_query_time search option:

    1. ... WHERE query='test;maxquerytime=1000;';

  • groupby - group-by function and attribute. Read this about grouping search results:

    1. ... WHERE query='test;groupby=day:published_ts;';
    2. ... WHERE query='test;groupby=attr:group_id;';

  • groupsort - group-by sorting clause:

    1. ... WHERE query='test;groupsort=@count desc;';

  • distinct - an attribute to compute COUNT(DISTINCT)) for when doing group-by:

    1. ... WHERE query='test;groupby=attr:country_id;distinct=site_id';

  • indexweights - comma-separated list of table names and weights to use when searching through several tables:

    1. ... WHERE query='test;indexweights=tbl_exact,2,tbl_stemmed,1;';

  • fieldweights - comma-separated list of per-field weights that can be used by the ranker:

    1. ... WHERE query='test;fieldweights=title,10,abstract,3,content,1;';

  • comment - a string to mark this query in query log, as in comment search option:

    1. ... WHERE query='test;comment=marker001;';

  • select - a string with expressions to compute:

    1. ... WHERE query='test;select=2*a+3*** as myexpr;';

  • host, port - remote searchd host name and TCP port, respectively:

    1. ... WHERE query='test;host=sphinx-test.loc;port=7312;';

  • ranker - a ranking function to use with “extended” matching mode, as in ranker. Known values are “proximity_bm25”, “bm25”, “none”, “wordcount”, “proximity”, “matchany”, “fieldmask”, “sph04”, “expr:EXPRESSION” syntax to support expression-based ranker (where EXPRESSION should be replaced with your specific ranking formula), and “export:EXPRESSION”:

    1. ... WHERE query='test;mode=extended;ranker=bm25;';
    2. ... WHERE query='test;mode=extended;ranker=expr:sum(lcs);';

    The “export” ranker works exactly like ranker=expr, but it stores the per-document factor values, while ranker=expr discards them after computing the final WEIGHT() value. Note that ranker=export is meant to be used but rarely, only to train a ML (machine learning) function or to define your own ranking function by hand, and never in actual production. When using this ranker, you’ll probably want to examine the output of the RANKFACTORS() function that produces a string with all the field level factors for each document.

  • sql

sql

  1. SELECT *, WEIGHT(), RANKFACTORS()
  2.     FROM myindex
  3.     WHERE MATCH('dog')
  4.     OPTION ranker=export('100*bm25');

Response

  1. *************************** 1\. row ***************************
  2.            id: 555617
  3.     published: 1110067331
  4.    channel_id: 1059819
  5.         title: 7
  6.       content: 428
  7.      weight(): 69900
  8. rankfactors(): bm25=699, bm25a=0.666478, field_mask=2,
  9. doc_word_count=1, field1=(lcs=1, hit_count=4, word_count=1,
  10. tf_idf=1.038127, min_idf=0.259532, max_idf=0.259532, sum_idf=0.259532,
  11. min_hit_pos=120, min_best_span_pos=120, exact_hit=0,
  12. max_window_hits=1), word1=(tf=4, idf=0.259532)
  13. *************************** 2\. row ***************************
  14.            id: 555313
  15.     published: 1108438365
  16.    channel_id: 1058561
  17.         title: 8
  18.       content: 249
  19.      weight(): 68500
  20. rankfactors(): bm25=685, bm25a=0.675213, field_mask=3,
  21. doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1,
  22. tf_idf=0.259532, min_idf=0.259532, max_idf=0.259532, sum_idf=0.259532,
  23. min_hit_pos=8, min_best_span_pos=8, exact_hit=0, max_window_hits=1),
  24. field1=(lcs=1, hit_count=2, word_count=1, tf_idf=0.519063,
  25. min_idf=0.259532, max_idf=0.259532, sum_idf=0.259532, min_hit_pos=36,
  26. min_best_span_pos=36, exact_hit=0, max_window_hits=1), word1=(tf=3,
  27. idf=0.259532)

  • geoanchor - geodistance anchor. Read more about Geo-search in this section. Takes 4 parameters which are latitude and longitude attribute names, and anchor point coordinates respectively:

    1. ... WHERE query='test;geoanchor=latattr,lonattr,0.123,0.456';

One very important note that it is much more efficient to allow Manticore to perform sorting, filtering and slicing the result set than to raise max matches count and use WHERE, ORDER BY and LIMIT clauses on MySQL side. This is for two reasons. First, Manticore does a number of optimizations and performs better than MySQL on these tasks. Second, less data would need to be packed by searchd, transferred and unpacked by SphinxSE.

Additional query info besides result set could be retrieved with SHOW ENGINE SPHINX STATUS statement:

  • sql

sql

  1. mysql> SHOW ENGINE SPHINX STATUS;

Response

  1. +--------+-------+-------------------------------------------------+
  2. | Type   | Name  | Status                                          |
  3. +--------+-------+-------------------------------------------------+
  4. | SPHINX | stats | total: 25, total found: 25, time: 126, words: 2 |
  5. | SPHINX | words | sphinx:591:1256 soft:11076:15945                |
  6. +--------+-------+-------------------------------------------------+
  7. 2 rows in set (0.00 sec)

This information can also be accessed through status variables. Note that this method does not require super-user privileges.

  • sql

sql

  1. mysql> SHOW STATUS LIKE 'sphinx_%';

Response

  1. +--------------------+----------------------------------+
  2. | Variable_name      | Value                            |
  3. +--------------------+----------------------------------+
  4. | sphinx_total       | 25                               |
  5. | sphinx_total_found | 25                               |
  6. | sphinx_time        | 126                              |
  7. | sphinx_word_count  | 2                                |
  8. | sphinx_words       | sphinx:591:1256 soft:11076:15945 |
  9. +--------------------+----------------------------------+
  10. 5 rows in set (0.00 sec)

You could perform JOINs on SphinxSE search table and tables using other engines. Here’s an example with “documents” from example.sql:

  • sql

sql

  1. mysql> SELECT content, date_added FROM test.documents docs
  2. -> JOIN t1 ON (docs.id=t1.id)
  3. -> WHERE query="one document;mode=any";
  5. mysql> SHOW ENGINE SPHINX STATUS;

Response

  1. +-------------------------------------+---------------------+
  2. | content                             | docdate             |
  3. +-------------------------------------+---------------------+
  4. | this is my test document number two | 2006-06-17 14:04:28 |
  5. | this is my test document number one | 2006-06-17 14:04:28 |
  6. +-------------------------------------+---------------------+
  7. 2 rows in set (0.00 sec)
  9. +--------+-------+---------------------------------------------+
  10. | Type   | Name  | Status                                      |
  11. +--------+-------+---------------------------------------------+
  12. | SPHINX | stats | total: 2, total found: 2, time: 0, words: 2 |
  13. | SPHINX | words | one:1:2 document:2:2                        |
  14. +--------+-------+---------------------------------------------+
  15. 2 rows in set (0.00 sec)

Building snippets via MySQL

SphinxSE also includes a UDF function that lets you create snippets through MySQL. The functionality is similar to HIGHLIGHT(), but accessible through MySQL+SphinxSE.

The binary that provides the UDF is named sphinx.so and should be automatically built and installed to proper location along with SphinxSE itself. If it does not get installed automatically for some reason, look for sphinx.so in the build directory and copy it to the plugins directory of your MySQL instance. After that, register the UDF using the following statement:

  1. CREATE FUNCTION sphinx_snippets RETURNS STRING SONAME 'sphinx.so';

Function name must be sphinx_snippets, you can not use an arbitrary name. Function arguments are as follows:

Prototype: function sphinx_snippets ( document, table, words [, options] );

Document and words arguments can be either strings or table columns. Options must be specified like this: 'value' AS option_name. For a list of supported options, refer to Highlighting section. The only UDF-specific additional option is named sphinx and lets you specify searchd location (host and port).

Usage examples:

  1. SELECT sphinx_snippets('hello world doc', 'main', 'world',
  2.     'sphinx://192.168.1.1/' AS sphinx, true AS exact_phrase,
  3.     '[**]' AS before_match, '[/**]' AS after_match)
  4. FROM documents;
  6. SELECT title, sphinx_snippets(text, 'index', 'mysql php') AS text
  7.     FROM sphinx, documents
  8.     WHERE query='mysql php' AND sphinx.id=documents.id;

FEDERATED

Using MySQL FEDERATED engine you can connect to a local or remote Manticore instance from MySQL/MariaDB and perform search queries.

Using FEDERATED

An actual Manticore query cannot be used directly with FEDERATED engine and must be “proxied” (sent as a string in a column) due to limitations of the FEDERATED engine and the fact that Manticore implements custom syntax like the MATCH clause.

To search via FEDERATED, you would need to create first a FEDERATED engine table. The Manticore query will be included in a query column in the SELECT performed over the FEDERATED table.

Creating FEDERATED compatible MySQL table:

  • SQL

SQL

  1. CREATE TABLE t1
  2. (
  3.     id          INTEGER UNSIGNED NOT NULL,
  4.     year        INTEGER NOT NULL,
  5.     rating      FLOAT,
  6.     query       VARCHAR(1024) NOT NULL,
  7.     INDEX(query)
  8. ) ENGINE=FEDERATED
  9. DEFAULT CHARSET=utf8
  10. CONNECTION='mysql://FEDERATED@127.0.0.1:9306/DB/movies';

Response

  1. Query OK, 0 rows affected (0.00 sec)

Query FEDERATED compatible table:

  • SQL

SQL

  1. SELECT * FROM t1 WHERE query='SELECT * FROM movies WHERE MATCH (\'pie\')';

Response

  1. +----+------+--------+------------------------------------------+
  2. | id | year | rating | query                                    |
  3. +----+------+--------+------------------------------------------+
  4. |  1 | 2019 |      5 | SELECT * FROM movies WHERE MATCH ('pie') |
  5. +----+------+--------+------------------------------------------+
  6. 1 row in set (0.04 sec)

The only fixed mapping is query column. It is mandatory and must be the only column with a table attached.

The Manticore table that is linked via FEDERATED must be a physical table (plain or real-time).

The FEDERATED table should have columns with the same names as a remote Manticore table attributes as it will be bound to the attributes provided in Manticore result set by name, however it might map not all attributes, but only some of them.

Manticore server identifies query from a FEDERATED client by user name “FEDERATED”. CONNECTION string parameter is to be used to specify Manticore host, SQL port and tables for queries coming through the connection. The connection string syntax is as follows:

  1. CONNECTION="mysql://FEDERATED@HOST:PORT/DB/TABLENAME"

Since Manticore doesn’t have the concept of database, the DB string can be random as it will be ignored by Manticore, but MySQL requires a value in the CONNECTION string definition. As seen in the example, full SELECT SQL query should be put into a WHERE clause against column query.

Only SELECT statement is supported, not INSERT, REPLACE, UPDATE, DELETE.

FEDERATED tips

One very important note that it is much more efficient to allow Manticore to perform sorting, filtering and slicing the result set than to raise max matches count and use WHERE, ORDER BY and LIMIT clauses on MySQL side. This is for two reasons. First, Manticore does a number of optimizations and performs better than MySQL on these tasks. Second, less data would need to be packed by searchd, transferred and unpacked between Manticore and MySQL.

JOINs can be performed between FEDERATED table and other MySQL tables. This can be used to retrieve information that is not stored in a Manticore table.

  • SQL

SQL

Query to JOIN MySQL based table with FEDERATED table served by Manticore:
  1. SELECT t1.id, t1.year, comments.comment FROM t1 JOIN comments ON t1.id=comments.post_id WHERE query='SELECT * FROM movies WHERE MATCH (\'pie\')';

Response

  1. +----+------+--------------+
  2. | id | year | comment      |
  3. +----+------+--------------+
  4. |  1 | 2019 | was not good |
  5. +----+------+--------------+
  6. 1 row in set (0.00 sec)