Difference between revisions of "Sphinx"

From Doku
Line 1: Line 1:
 
Creating and maintaining a Sphinx index on your MySQL table.
 
Creating and maintaining a Sphinx index on your MySQL table.
  
=== Installing Sphinx alongside MariaDB
+
=== Installing Sphinx alongside MariaDB ===
  
 
Download and install from binaries.
 
Download and install from binaries.
Line 16: Line 16:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=== Adding a MariaDB internal client for Sphinx
+
=== Adding a MariaDB internal client for Sphinx ===
  
 
In MariaDB:
 
In MariaDB:
Line 57: Line 57:
  
  
=== Creating an index with MySQL source.
+
=== Creating an index with MySQL source. ===
=== Populating the index.
+
=== Populating the index. ===
=== Updating the index.
+
=== Updating the index. ===
  
=== Creating a Real Time index.
+
=== Creating a Real Time index. ===
=== Updating the index.
+
=== Updating the index. ===
  
=== Querying the index.
+
=== Querying the index. ===
  
==== Querying the index via direct connection to searchd daemon.
+
==== Querying the index via direct connection to searchd daemon. ====
  
==== Querying the index alone from within MariaDB.
+
==== Querying the index alone from within MariaDB. ====
  
 
<syntaxhighlight lang="SQL">
 
<syntaxhighlight lang="SQL">
Line 74: Line 74:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
==== Joining the results with MariaDB records.
+
==== Joining the results with MariaDB records. ====
  
 
Querying the index but fetching data directly from our MariaDB equivalent table:
 
Querying the index but fetching data directly from our MariaDB equivalent table:

Revision as of 10:15, 2 November 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"


Creating an index with MySQL source.

Populating the index.

Updating the index.

Creating a Real Time index.

Updating the index.

Querying the index.

Querying the index via direct connection to searchd daemon.

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";