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 12: Line 12:
 
[[sphinx.conf|sphinx.conf example]]
 
[[sphinx.conf|sphinx.conf example]]
  
 +
<syntaxhighlight>
 
chown -R sphinxsearch:sphinxsearch /var/sphinxsearch
 
chown -R sphinxsearch:sphinxsearch /var/sphinxsearch
 +
</syntaxhighlight>
  
* Adding a MariaDB internal client for Sphinx
+
=== Adding a MariaDB internal client for Sphinx
  
 
In MariaDB:
 
In MariaDB:
  
`INSTALL SONAME 'ha_sphinx';(only once, to install the engine)
+
<syntaxhighlight lang="SQL">
 +
INSTALL SONAME 'ha_sphinx'; -- (only once, to install the engine)
 +
</syntaxhighlight>
  
`CREATE DATABASE sphinx;` (only once per project, could be named sphinx_sku)
+
<syntaxhighlight lang="SQL">
 +
CREATE DATABASE sphinx; -- (only once per project, could be named sphinx_sku)
 +
</syntaxhighlight>
  
 +
<syntaxhighlight lang="SQL">
 
USE sphinx;
 
USE sphinx;
 
CREATE TABLE sku_attributes
 
CREATE TABLE sku_attributes
Line 31: Line 38:
 
     INDEX(query)
 
     INDEX(query)
 
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/sku_attributes";
 
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/sku_attributes";
 +
</syntaxhighlight>
  
 
Once every time we add an index to Sphinx.  This table is our MariaDB gateway to query the Sphinx index.
 
Once every time we add an index to Sphinx.  This table is our MariaDB gateway to query the Sphinx index.
  
 +
Adding another index:
  
 
+
<syntaxhighlight lang="SQL">
Adding another index:
 
 
USE sphinx;
 
USE sphinx;
 
CREATE TABLE sku_products
 
CREATE TABLE sku_products
Line 46: Line 54:
 
     INDEX(query)
 
     INDEX(query)
 
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/sku_products"
 
) ENGINE=SPHINX CONNECTION="sphinx://127.0.0.1:9312/sku_products"
 +
</syntaxhighlight>
 +
 +
 +
=== Creating an index with MySQL source.
 +
=== Populating the index.
 +
=== Updating the index.
  
 +
=== Creating a Real Time index.
 +
=== Updating the index.
  
* Creating an index with MySQL source.
+
=== Querying the index.
* Populating the index.
 
* Updating the index.
 
  
* Creating a Real Time index.
+
==== Querying the index via direct connection to searchd daemon.
* Updating the index.
 
  
* Querying the index alone.
+
==== Querying the index alone from within MariaDB.
  
`SELECT * FROM sphinx.sku_attributes WHERE query="simple and easy to install";`
+
<syntaxhighlight lang="SQL">
 +
SELECT * FROM sphinx.sku_attributes WHERE query="simple and easy to install";
 +
</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:
  
 +
<syntaxhighlight lang="SQL">
 
SELECT * FROM sphinx.sku_attributes sp_a LEFT JOIN sku_io.product_attributes pa ON pa.id = sp_a.i
 
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";
 
d WHERE sp_a.query="simple and easy to install";
 +
</syntaxhighlight>

Revision as of 10:13, 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";