Sphinx

Written by Sebastián Grignoli, 2020

Creating and maintaining a Sphinx index on your MySQL table.

Installing Sphinx alongside MariaDB

Download and install from binaries.

Create a Linux user: `sphinxsearch` Create a folder /var/sphinxsearch/

Copy config file sphinx.conf to /var/sphinxsearch/ containing:

sphinx.conf example

chown -R sphinxsearch:sphinxsearch /var/sphinxsearch

Adding a MariaDB internal client for Sphinx

In MariaDB:

INSTALL SONAME 'ha_sphinx';  -- (only once, to install the engine)

CREATE DATABASE sphinx; -- (only once per project, could be named sphinx_sku)

USE sphinx;
CREATE TABLE sku_attributes
(
    id BIGINT UNSIGNED NOT NULL,
    weight  INTEGER NOT NULL,
    query   VARCHAR(3072) NOT NULL,
    group_id  INTEGER,
    INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/sku_attributes";

Once every time we add an index to Sphinx. This table is our MariaDB gateway to query the Sphinx index.

Adding another index:

USE sphinx;
CREATE TABLE sku_products
(
    id BIGINT UNSIGNED NOT NULL,
    weight  INTEGER NOT NULL,
    query   VARCHAR(3072) NOT NULL,
    group_id  INTEGER,
    INDEX(query)
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/sku_products"

Querying the index.

Querying the index alone from within MariaDB.

SELECT * FROM sphinx.sku_attributes WHERE query="simple and easy to install";

Joining the results with MariaDB records.

Querying the index but fetching data directly from our MariaDB equivalent table:

SELECT * FROM sphinx.sku_attributes sp_a LEFT JOIN sku_io.product_attributes pa ON pa.id = sp_a.i
d WHERE sp_a.query="simple and easy to install";