With any growing or evolving database, there comes a time when the limitations of MariaDB's built in, full text-searching functionality becomes more of a hindrance than its convenience is worth. At that point, another method is needed to efficiently index and search through our textual data. This is where Sphinx comes in.
There are actually two parts to Sphinx: an external daemon called Sphinx that does the work of building and maintaining the search index using that we use to search our data, and a storage engine component called SphinxSE that is part of MariaDB, which the Sphinx daemon uses to talk to MariaDB. The recipes in this chapter will cover setting up and using both these.
Before we can start using Sphinx, we need to enable SphinxSE in MariaDB.
Open the
mysqlcommand-line client and connect to our database server with a user that has theSUPERprivilege.Run the following command to install
SphinxSE:INSTALL SONAME 'ha_sphinx';Run the following command, shown as follows, to check that the Sphinx Storage Engine (SphinxSE) is enabled (the
Supportcolumn will sayYES):SHOW storage engines;
Run the following command to view the SphinxSE status variables (they will be empty):
SHOW STATUS LIKE 'sphinx_%';
SphinxSE is included in MariaDB, but it is disabled by default. To enable it, we run the INSTALL SONAME command with the name of the plugin (ha_sphinx). This is a one-time operation.
Once SphinxSE is enabled in this manner and if we have the external Sphinx daemon installed and running, we can start using Sphinx to search our data. This is the topic of the following two recipes.
In order to use SphinxSE and Sphinx, we must install the daemon on our server. This recipe covers the process for Linux servers running Ubuntu, Debian, Red Hat, Fedora, or CentOS.
On Red Hat, CentOS, Ubuntu, or Debian, go to the Sphinx download site at http://sphinxsearch.com/downloads/release/ and download the latest Sphinx package for our Linux distribution.
On Debian or Ubuntu servers, run the following statement to install the Sphinx daemon:
sudo apt-get install unixodbc libpq5 mariadb-client sudo dpkg -i sphinxsearch*.deb
On Red Hat and CentOS, run the following statement to install the Sphinx daemon:
sudo yum install postgresql-libs unixODBC sudo rpm -Uhv sphinx*.rpm
On Fedora, run the following command to install Sphinx:
sudo yum install sphinxOn all server types, configure the Sphinx daemon as described in the Configuring the Sphinx daemon recipe in this chapter.
On Ubuntu and Debian, edit the
/etc/default/sphinxsearchfile and set...
In order to use Sphinx, we must install the daemon on our server. This recipe is all about installing the Windows version of the Sphinx daemon.
Go to http://sphinxsearch.com/downloads/release/ and download the latest version of Sphinx for MySQL, either the 64-bit or 32-bit version depending on our version of Windows. For this recipe, we'll assume that we're running a 64-bit version of Windows.
Navigate to the
Downloadsfolder and extract the ZIP file.Extract the file to a location that is convenient. This recipe will assume that we extracted the files to
C:\Sphinx(as the Sphinx docs recommend).If the unzipping process creates a Sphinx subfolder (such as
sphinx-2.1.3-release-win64) underC:\Sphinxmove the contents of that subdirectory to theC:\Sphinxfolder and then remove the empty directory using the following statements:cd C:\Sphinx mv .\sphinx-2.1.3-release-win64\* .\ rmdir sphinx-2.1.3-release-win64
Configure Sphinx as described...
In order to use Sphinx, we need to add a user to our MariaDB database and configure the Sphinx daemon so that it indexes the content we want it to.
Install the SphinxSE as described in the Installing SphinxSE in MariaDB recipe in this chapter. Install the Sphinx daemon as described in either the Installing the Sphinx daemon on Linux recipe or the Installing the Sphinx daemon on Windows recipe, both in this chapter, depending on which operating system we are using.
Open the
mysqlcommand-line client and create a user withSELECTrights for the tables in the databases we want the Sphinx daemon to index, using the following statements:CREATE USER 'sphinx'@localhost IDENTIFIED BY 'sphinxsecretpassword'; GRANT SELECT on test.* to 'sphinx'@localhost;
Move the default
sphinx.conffile out of the way; for example, the following will work on Linux:sudo mv -vi sphinx.conf sphinx.conf.dist
After completing the other recipes in this chapter, this is the recipe where we actually get to see Sphinx doing something.
This recipe requires that we install and configure SphinxSE and the Sphinx daemon. See the previous recipes in this chapter for instructions.
In the previous recipe, we configured the Sphinx daemon to index and search a table called documents in the test database. For the purposes of this recipe and to match the previous recipe, we need to create this table with the following CREATE TABLE command:
CREATE TABLE documents ( id SERIAL PRIMARY KEY, date_added TIMESTAMP, title VARCHAR(256), content TEXT );
We also need to add some example data to the table. The 4399OS_09_documents.sql file, available from this book's website, will create the table and populate it with some example data. We can load the file using the following statement:
mysql -u user -p test < 4399OS_09_documents.sql
We'll need...
© 2014 Packt Publishing Limited All Rights ReservedAuthor (1)
