Tag Archives: sphinx

MySQL: How do you enable sphinxse (Sphinx Storage Engine) in your mysql installation?

8 Replies

As you may know mysql fulltext search is not highly scalable.  One of the options to get around this scalability limitation, which I prefer, is to use Sphinx.  You can use Sphinx with out having to alter your mysql installation.  But, if you would like to use from within mysql and not have to worry about how to pass data between Sphinx and MySQL, you can enable sphinxse (sphinx storage engine).  It is not included with mysql by default so you will have to compile it yourself.

Here are the instructions on how to get sphinxse compiled with your mysql installation on CentOS x64.  I am sure same instructions will work for other flavors but I have not tested it.  I will be compiling the most current version of sphinx (0.9.8) with most current stable version of mysql (5.0.51b) at the time of the writing.  Let’s get the appropriate packages first:

wget http://www.sphinxsearch.com/downloads/sphinx-0.9.8.tar.gz
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.51b.tar.gz/from/http://mysql.he.net/
tar zxpf sphinx*
tar zxpf mysql*

You will also need “bison”, “patch”, “automake” and “libtool” installed.  Let us just do a yum install for it.

yum -y install bison patch automake libtool

NOTE:  if you don’t install bison, you will get the following error:
sed '/^#/ s|y\.tab\.c|sql_yacc.cc|' y.tab.c >sql_yacc.cct && mv sql_yacc.cct sql_yacc.cc
sed: can't read y.tab.c: No such file or directory
make[2]: *** [sql_yacc.cc] Error 2

Let us continue with patching mysql source with sphinx storage engine (sphinxse) code and compile/install our new binaries.

cd mysql*
patch -p1 < ../sphinx-0.9.8/mysqlse/sphinx.5.0.37.diff #Make sure everything succeeded.
BUILD/autorun.sh
mkdir sql/sphinx
cp ../sphinx-0.9.8/mysqlse/* sql/sphinx
./configure --prefix=/usr/local/mysql --with-sphinx-storage-engine
make
make install

Now start your mysql installtion and check if engine support is compiled in:

mysql> show engines\G
Engine: SPHINX
Support: YES
Comment: Sphinx storage engine 0.9.8

To read more about how to use Sphinx storage engine, please refer to:  Sphinx documentation for using sphinx storage engine

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.