Setup and configure Sphinx Search Engine – Part 1

Aneh Thakur

7 years ago

Last updated on August 28th, 2020 at 04:32 pm

Sphinx is one of the best open source search engine which is design for full text search. Sphinx design to perform full text search on the database with high performance and relevance search result.

Sphinx index data from various sources like MySql/SQL, plain text file, HTML file, Email etc. In this post, I will explain to you how you can setup and configure Sphinx search engine with your MySql Database and perform some search operation in it.

Key feature of Sphinx search engine

Real-Time full-text indexes
SQL database indexing
Non-SQL storage indexing
Easy application integration
Advanced full-text searching syntax
Rich database-like querying features
High indexing and searching performance
Proven scalability up to billions of documents, terabytes of data, and thousands of queries per second

There also lots more other feature of Sphinx search you can check the complete list of them on this link.

Database support by Sphinx

Sphinx currently supports MySQL, PostgreSQL, and ODBC-compliant databases as data sources natively. Other data sources can be indexed via pipe in a custom XML format.

Alternate Search Engine like Sphinx

Solr
ElasticSearch
Postgresql built-in full text search
MySQl built-in full text search

Why I use Sphinx?

In my case currently, i am using MySql Built in full text search in one of my E-commerce site. The main issue, I am facing is the search result. Search results are not relevant most of the time so, I try sphinx and now its working fine.

With the help of Sphinx search can create your small Google like a search engine.

For this tutorial, I am using Linux with MySql so let get started with main part of this tutorial. You can follow below simple steps to setup an configure Sphinx search.

Step 1. Open terminal and install Sphinx using below command

sudo apt-get install sphinxsearch

Step 2. Once setup was complete we need to create a database in my case

--
-- Database: `sphinx_demo`
--
CREATE DATABASE IF NOT EXISTS `sphinx_demo` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `sphinx_demo`;

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `design_no` varchar(32) NOT NULL,
  `album_id` int(10) NOT NULL,
  `catalogno` text NOT NULL,
  `brand` text NOT NULL,
  `product` text NOT NULL,
  `fabric` text NOT NULL,
  `stock` text NOT NULL,
  `total_products` varchar(32) NOT NULL,
  `Image` text NOT NULL,
  `subcategory` varchar(50) NOT NULL,
  `category_id` varchar(32) NOT NULL,
  `wholesale_price` varchar(100) NOT NULL,
  `weight` varchar(10) NOT NULL,
  `fcolor` text NOT NULL,
  `fwork` text NOT NULL,
  `ffabric` text NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `design_no`, `album_id`, `catalogno`, `brand`, `product`, `fabric`, `stock`, `total_products`, `Image`, `subcategory`, `category_id`, `wholesale_price`, `weight`, `fcolor`, `fwork`, `ffabric`, `created`) VALUES
(1, '182A', 1, '1857', 'Jashan', 'Jashan R812 New Colors red', 'Top - Georgette , Inner - Santoon , Dupatta - Nazmin', 'Available', '10', '1857-182A.JPG', 'Suit', '1', '1750', '1', '', '', '', '2017-09-02 05:03:15'),
(2, '182B', 1, '1857', 'Jashan', 'Jashan R812 New Colors', 'Top - Georgette , Inner - Santoon , Dupatta - Nazmin', 'Available', '10', '1857-182B.JPG', 'Suit', '1', '1750', '1', '', '', '', '2017-09-02 05:03:15'),
(3, '182C', 1, '1857', 'Jashan', 'Jashan R812 New Colors blue', 'Top - Georgette , Inner - Santoon , Dupatta - Nazmin', 'Available', '10', '1857-182C.JPG', 'Suit', '1', '1750', '1', '', '', '', '2017-09-02 05:03:15'),
(4, '182D', 1, '1857', 'Jashan', 'Jashan R812 New Colors', 'Top - Georgette , Inner - Santoon , Dupatta - Nazmin', 'Available', '10', '1857-182D.JPG', 'Suit', '1', '1750', '1', '', '', '', '2017-09-02 05:03:15'),
(5, '23001', 2, '1002', 'Arihant Designer', 'Hamom Vol 10', 'Faux Geogette Lehenga - net , Inner - Santon', 'Ready To Ship', '10', '1856-23001.JPG', 'Suit', '1', '1635', '1', '', '', '', '2017-09-02 05:03:15'),
(6, '23002', 2, '1002', 'Arihant Designer', 'Hamom Vol 10', 'Faux Geogette Lehenga - net , Inner - Santon', 'Ready To Ship', '10', '1856-23002.JPG', 'Suit', '1', '1640', '1', '', '', '', '2017-09-02 05:03:15'),
(7, '23003', 2, '1002', 'Arihant Designer', 'Hamom Vol 10', 'Faux Geogette Lehenga - net , Inner - Santon', 'Ready To Ship', '10', '1856-23003.JPG', 'Suit', '1', '1575', '1', '', '', '', '2017-09-02 05:03:15'),
(8, '23004', 2, '1002', 'Arihant Designer', 'Hamom Vol 10', 'Faux Geogette Lehenga - net , Inner - Santon', 'Ready To Ship', '10', '1856-23004.JPG', 'Suit', '1', '1545', '1', '', '', '', '2017-09-02 05:03:15'),
(9, '23005', 2, '1002', 'Arihant Designer', 'Hamom Vol 10', 'Faux Geogette Lehenga - net , Inner - Santon', 'Ready To Ship', '10', '1856-23005.JPG', 'Suit', '1', '1595', '1', '', '', '', '2017-09-02 05:03:15'),
(10, '23006', 2, '1002', 'Arihant Designer', 'Hamom Vol 10', 'Faux Geogette Lehenga - net , Inner - Santon', 'Ready To Ship', '10', '1856-23006.JPG', 'Suit', '1', '1670', '1', '', '', '', '2017-09-02 05:03:15'),
(11, '1121', 1, '1874', 'Aardhngini', 'Ardhagini Saree Shashi Series 1121 to 113', 'Fancy', 'Available', '10', '1856-23006.JPG', 'gown', '1', '1695', '1', 'Red', 'Stone', 'Net', '2017-09-02 05:04:04');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

I am going to use Sphinx for product search so I create sample data for product.

Step 3. Now we can configure Sphinx file. You can run below command

sudo nano /etc/sphinxsearch/sphinx.conf

Now you can add below code in your config file

source src1
{
  type          = mysql
  sql_host      = localhost
  sql_user      = root
  sql_pass      = Your_Password
  sql_db        = stock
  sql_port      = 3306

  sql_query     = \
  SELECT id, design_no, album_id, product, fabric, stock, UNIX_TIMESTAMP(created) AS date_added \
  FROM products

  sql_attr_uint    = id
  sql_field_string = design_no
  sql_field_string = album_id
  sql_field_string = product
  sql_field_string = fabric
  sql_field_string = stock
  sql_attr_timestamp = date_added
}

index test1
{
  source            = src1
  path              = /var/lib/sphinxsearch/data/test1
  docinfo           = extern
}

searchd
{
  listen            = 9306:mysql41
  log               = /var/log/sphinxsearch/searchd.log
  query_log         = /var/log/sphinxsearch/query.log
  read_timeout      = 5
  max_children      = 30
  pid_file          = /var/run/sphinxsearch/searchd.pid
  seamless_rotate   = 1
  preopen_indexes   = 1
  unlink_old        = 1
  binlog_path       = /var/lib/sphinxsearch/data
}

Source block in configuration file contains information of data source like your MySql Server details. [rad-hl]sql_query[/rad-hl] should be a unique id. The SQL query will run on every index and dump the data to Sphinx index file. Below are the descriptions of each field and the source block itself.

type: Type of data source to index. In our example, this is mysql. Other supported types include pgsql, mssql, xmlpipe2, odbc, and more.
sql_host: Hostname for the MySQL host. In our example, this is localhost. This can be a domain or IP address.
sql_user: Username for the MySQL login. In our example, this is root.
sql_pass: Password for the MySQL user. In our example, this is the root MySQL user’s password.
sql_db: Name of the database that stores data. In our example, this is test.
sql_query: The query thats dumps data from the database to the index.

source src1
{
  type          = mysql
  sql_host      = localhost
  sql_user      = root
  sql_pass      = Your_Password
  sql_db        = stock
  sql_port      = 3306

  sql_query     = \
  SELECT id, design_no, album_id, product, fabric, stock, UNIX_TIMESTAMP(created) AS date_added \
  FROM products

  sql_attr_uint    = id
  sql_field_string = design_no
  sql_field_string = album_id
  sql_field_string = product
  sql_field_string = fabric
  sql_field_string = stock
  sql_attr_timestamp = date_added
}

The index component contains the source and the path to store the data.
in

source: Name of the source block. In our example, this is src1.
path: The path to save the index.

index test1
{
  source            = src1
  path              = /var/lib/sphinxsearch/data/test1
  docinfo           = extern
}

The searchd component contains the port and other variables to run the Sphinx daemon.

listen: The port which the Sphinx daemon will run, followed by the protocol. In our example, this is 9306:mysql41. Known protocols are :sphinx (SphinxAPI) and :mysql41 (SphinxQL)
query_log: The path to save the query log.
pid_file: The path to PID file of Sphinx daemon.
seamless_rotate: Prevents searchd stalls while rotating indexes with huge amounts of data to precache.
preopen_indexes: Whether to forcibly preopen all indexes on startup.
unlink_old: Whether to delete old index copies on successful rotation.

searchd
{
  listen            = 9306:mysql41
  log               = /var/log/sphinxsearch/searchd.log
  query_log         = /var/log/sphinxsearch/query.log
  read_timeout      = 5
  max_children      = 30
  pid_file          = /var/run/sphinxsearch/searchd.pid
  seamless_rotate   = 1
  preopen_indexes   = 1
  unlink_old        = 1
  binlog_path       = /var/lib/sphinxsearch/data
}

Step 4. Now we can index our database to Sphinx index you can also use [rad-hl]cron[/rad-hl] job to update index data. Run below command to add data to index.

sudo indexer --all --rotate

You should get output like image

Step 5. Now we need to turn on Sphinx daemon by default it is off you can turn on by running below command

sudo sed -i 's/START=no/START=yes/g' /etc/default/sphinxsearch

after above command run below command to restart Sphinx

sudo systemctl restart sphinxsearch.service
sudo systemctl status sphinxsearch.service

Step 6. Test your configuration, to test first run below command in terminal

mysql -h0 -P9306

now we can search for some keyword. To search you can run below command

SELECT * FROM test1 WHERE MATCH('saree'); SHOW META;

You should get something that looks like the following.

Now if you want to search keyword you can run below command

CALL KEYWORDS ('blue color', 'test1', 1);

That’s all 🙂 hope you like this. If this post help then Like and share this post. Stay tune in my nexxt post I will explian how we can use Sphinx with PHP.

Key feature of Sphinx search engine

Database support by Sphinx

Alternate Search Engine like Sphinx

Why I use Sphinx?

Share this: