Difference between revisions of "Sphinx"
(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:
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";