Sphinx is a full-text search engine. It currently works with MySQL and PostgreSQL better known as Postgres. The two most popular options when its comes to open source databases if not databases in general. Both MySQL and PostgreSQL have full-text search capabilities. So why bother with sphinx?
- Control – With sphinx you get more control over filtering, for example you can set a certain column to have a higher weight by using just one function
- Speed – Sphinx is about 40-100 times faster at indexing. It’s also up a 1000 times faster at searching than MySQL and Postgres depending on the data.
- Relevance – You get much more relevant results with sphinx. i.e Accuracy is greatly improved
- Scalability – Sphinx indexes can be stored on distributed servers. You can also search multiple sites at one go.
I could go on but those are probably the most important reasons to switch to Sphinx SE. Besides the fat that there are loads of “big” sites already using it. The Api currently supports PHP, Perl, Ruby, and Java.
So down to the important bits. Btw this tutorial is based on Sphinx 0.9.9 but it shouldn’t matter
Step 1: Download sphinx
The latest stable release at the time of this article is Sphinx 0.9.9 (r2117; Dec 02, 2009) I prefer to use wget so in the terminal I type
$ cd ~ $ mkdir sphinxtemp $ cd sphinxtemp $ wget http://www.sphinxsearch.com/downloads/sphinx-0.9.9.tar.gz
Step 2: Unzip Sphinx
$ tar -xvf sphinx-0.9.9.tar.gz $ cd sphinx-0.9.9
Step 3: Install Sphinx
Ones in the directory sphinx-0.9.9 type
At this point you might get an error like this
** ERROR: cannot find MySQL include files. Check that you do have MySQL include files installed. The package name is typically 'mysql-devel'. If include files are installed on your system, but you are still getting this message, you should do one of the following: 1) either specify includes location explicitly, using --with-mysql-includes; 2) or specify MySQL installation root location explicitly, using --with-mysql; 3) or make sure that the path to 'mysql_config' program is listed in your PATH environment variable.
You need to install mysql-devel. I think it also goes my different names also. I think Ubuntu calls it libmysqlclient15-dev asdf
In Centos linux all i had to was type
$ yum install mysql-devel
$ sudo aptitude install libmysqlclient15-dev
After install the above package, the following commands
$ ./configure $ make $ make install
Voila! You now have sphinx installed
There will be three new binaries stored here /usr/local/bin/ namely:
- indexer – this is the indexer you will run with configuration settings to index a database
- searchd – this is the search damon that will be listening to connection from your script(php, phython…), it has to be running for anything to happen
- search – this is used to run search in the terminal
Step 4: Configure Sphinx
The installation will also create a bunch of files in /usr/local/etc
example.sql, sphinx.conf.dist, sphinx-min.conf.dist
SubStep 4.1 Create Sphinx config file
Copy /usr/local/etc/sphinx-min.conf to /usr/local/etc/sphinx.conf (In the same folder)
cp /usr/local/etc/sphinx-min.conf /usr/local/etc/sphinx.conf
SubStep 4.2 Create Index
Open up /usr/local/etc/sphinx.conf in you favourite editor(vi, gedit, kate, whatever).
Configure this part in the source to represent your server configuration
sql_host = localhost sql_user = test sql_pass = sql_db = test
sql_query is what you want the indexer to index.
So say you have a table of books like this in your database:
|1||Blogging 101||Blogging 101 is the bestest blogging book ever. Awesome.|
|2||PHP Unleashed||PHP development book for total beginners|
|3||The Chicken and the Pig||This is a book about an unlikely love affair between a pig and a chicken|
Assuming you want to index all columns:
sql_query = select * from books
sql_query_info = select * from books where book_id=$id
SubStep 4.3 Run the Indexer
To run the indexer just type
make sure you stop the search daemon first like this
$ searchd --stop
In the Linux terminal. It should index at this point. If it doesn’t you missed something. And it will give an error message. Good luck.
After Indexing has completed start the Search Daemon again
To test sphinx, just type search term e.g:
$ search chicken
This should return one row. “The chicken and the pig”
I will be writing a more detailed tutorial on the PHP API so you can integrate sphinx into your php code. The php api is
For now, Adios. 🙂