How to Install and Configure Sphinx on Ubuntu

Sphinx is a full-text search engine, publicly distributed under GPL version 2. Commercial licensing (eg. for embedded use) is available upon request. You can use Sphinx for creating big data indexes. It supports MySQL, PostgreSQL, ODBC. It is written in C++ programming language and works with Linux and other popular operating systems.

In this tutorial I will show how to install and configure Sphinx on Ubuntu operating system.

Prerequisites

Before starting this guide, we must have the followings.

• 1. Ubuntu server with sudo access.
• 2. MySQL server should be installed on your system.

Now follow the below steps to install and configure Sphinx on Ubuntu.

Step #1 : Sphinx Installation

You can easily install Sphinx on your Ubuntu system. It is available in the native package repository. You can install it using the apt-get package manager on your Ubuntu system as shown below.

$  add-apt-repository ppa:builds/sphinxsearch-rel22
$ sudo apt-get update
$ sudo apt-get install sphinxsearch

Step #2 : Import MySQL Database

First of all create a database named ” test ” in your MySQL server and then import the sample SQL file into this. Now restore the database provided by the Sphinx search package.

$ sudo mysqladmin -u root -p create test
$ sudo mysql -u root -p test < /usr/share/doc/sphinxsearch/example-conf/example.sql

Step #3 : Sphinx Configuration

Now configure the Sphinx as shown below and provide the MySQL details for connection.

$ sudo vi /etc/sphinxsearch/sphinx.conf

source src1
{
	# data source type. mandatory, no default value
	# known types are mysql, pgsql, mssql, xmlpipe, xmlpipe2, odbc
	type                    = mysql

	#####################################################################
	## SQL settings (for 'mysql' and 'pgsql' types)
	#####################################################################

	# some straightforward parameters for SQL source types
	sql_host                = localhost
	sql_user                = root
	sql_pass                = password
	sql_db                  = test
	sql_port                = 3306  # optional, default is 3306

Step #4 : Running Indexer

Now run the indexer to create the full-text index from your data. The indexer is the first of the two principal tools as part of Sphinx. It works for gathering the data that will be searchable. You will see the results like below.

$ sudo indexer --all 

Sphinx 2.2.11-id64-release (95ae9a6)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'test1'...
collected 4 docs, 0.0 MB
sorted 0.0 Mhits, 100.0% done
total 4 docs, 193 bytes
total 0.006 sec, 30791 bytes/sec, 638.16 docs/sec
indexing index 'test1stemmed'...
collected 4 docs, 0.0 MB
sorted 0.0 Mhits, 100.0% done
total 4 docs, 193 bytes
total 0.001 sec, 99382 bytes/sec, 2059.73 docs/sec
skipping non-plain index 'dist1'...
skipping non-plain index 'rt'...
total 8 reads, 0.000 sec, 0.1 kb/call avg, 0.0 msec/call avg
total 24 writes, 0.000 sec, 0.1 kb/call avg, 0.0 msec/call avg

Step #5 : Start Sphinx

Now start the Sphinx server and configure it for auto start on the system boot.

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

Start Sphinx service and check status.

$ service sphinxsearch start
$ service sphinxsearch status

Now set crontab for indexer to run it on a regular interval. In below example crontab will run on every hour.

$ crontab -e

0 * * * * /usr/bin/indexer --rotate --all

Save and close file.

Step #6 : Working with Sphinx

Now let’s run some queries on your Sphinx server. First of all connect to Sphinx MySQL server using the below command.

$ mysql -h0 -P9306

Now execute one by one command below and see the changes. This is for your own learning only.

mysql> SELECT * FROM test1 WHERE MATCH('document');
mysql> INSERT INTO rt VALUES (1, 'adding', 'sample text here', 11);
mysql> INSERT INTO rt VALUES (2, 'adding some more', 'sample text here', 22);
mysql> SELECT gid/11 FROM rt WHERE MATCH('sample') GROUP BY gid;
mysql> SELECT * FROM rt ORDER BY gid DESC;
mysql> SELECT *, WEIGHT() FROM test1 WHERE MATCH('"document one"/1');
mysql> SHOW META;
mysql> SET profiling=1;
mysql> SELECT * FROM test1 WHERE id IN (1,2,4);
mysql> SHOW PROFILE;
mysql> SELECT id, id%3 idd FROM test1 WHERE MATCH('this is | nothing') GROUP BY idd;
mysql> SHOW PROFILE;
mysql> SELECT id FROM test1 WHERE MATCH('is this a good plan?');
mysql> SHOW PLAN;
mysql> SELECT COUNT(*) FROM test1;
mysql> CALL KEYWORDS ('one two three', 'test1');
mysql> CALL KEYWORDS ('one two three', 'test1', 1);
mysql> SHOW TABLES;