Difference between revisions of "Sphinx"

From Doku
(Created page with "Creating and maintaining a Sphinx index on your MySQL table. * Installing Sphinx alongside MariaDB * Adding a MariaDB internal client for Sphinx * Creating an index with MyS...")
 
Line 2: Line 2:
  
 
* Installing Sphinx alongside MariaDB
 
* 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|sphinx.conf example]]
 +
 +
chown -R sphinxsearch:sphinxsearch /var/sphinxsearch
 +
 
* Adding a MariaDB internal client for Sphinx
 
* 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.
 
* Creating an index with MySQL source.
Line 12: Line 56:
  
 
* Querying the index alone.
 
* Querying the index alone.
 +
 +
`SELECT * FROM sphinx.sku_attributes WHERE query="simple and easy to install";`
 +
 
* Joining the results with MariaDB records.
 
* 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";

Revision as of 09:54, 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 alone.

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