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 islocalhost. 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.