Difference between revisions of "Sphinx"
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 | |
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 | |
In MariaDB: | In MariaDB: | ||
− | + | <syntaxhighlight lang="SQL"> | |
+ | INSTALL SONAME 'ha_sphinx'; -- (only once, to install the engine) | ||
+ | </syntaxhighlight> | ||
− | + | <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"> | |
− | |||
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. | ||
− | + | === Querying the index. | |
− | |||
− | |||
− | + | ==== Querying the index via direct connection to searchd daemon. | |
− | |||
− | + | ==== Querying the index alone from within MariaDB. | |
− | + | <syntaxhighlight lang="SQL"> | |
+ | SELECT * FROM sphinx.sku_attributes WHERE query="simple and easy to install"; | ||
+ | </syntaxhighlight> | ||
− | + | ==== 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:
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";