Configure Sphinx Full text search engine

Sphinx is open source search engine. Sphinx allow full-text searches. Sphinx is very efficient to perform search over large data. Sphinx can index data from various different sources like: MySQL, HTML, Text Files etc.

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.

Sphinx support no of database to index data. MySQL, PostgreSQL, and ODBC-compliant databases as data sources natively. Other data sources can be indexed via pipe in a custom XML format.

Where we use Sphinx?

We use sphinx for our ecommerce to search for product. Return most accurate data to user based on there query. We also create small search engine using Sphinx.

Installing and Setup Sphinx

Installation of Sphinx is very easy. You can follow bellow steps to setup sphinx.

Step 1. Open terminal and run below command

sudo apt-get install sphinxsearch

Step 2. Create database on your MySql server and run below query.

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

Step 3. Update Sphinx configuration run below code and update configuration

sudo nano /etc/sphinxsearch/sphinx.conf
// Add Below code in your config and save
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
}

Step 4. Now index your data to Sphinx server

sudo indexer --all --rotate

Step 5. Turn on Sphinx server

sudo sed -i 's/START=no/START=yes/g' /etc/default/sphinxsearch
sudo systemctl restart sphinxsearch.service
sudo systemctl status sphinxsearch.service

Step 6. Test your configuration

mysql -h0 -P9306
// Run query
SELECT * FROM test1 WHERE MATCH('saree'); SHOW META;

Output:-

You can read complete information about setting up Sphinx Server here