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

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

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

Now you can add below code in your config file

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.

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

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

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.

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.

You should get output like image

Shinx search setup and configration

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

after above command run below command to restart Sphinx

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

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

You should get something that looks like the following.

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

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.