Sphinx is a popular open source full text indexing engine which can be used to greatly improve search functionality over the full-text search options available in most databases. While adding this to the search function of a site recently, I stumbled upon some neat tricks that I would like to share with you.
Sphinx has developed a MySQL plugin (SphinxSE) which acts as a storage engine. This allows you to create a “search” table in your database which can be queried just like a regular table, and can even be used in JOIN operations. This plugin is usable in MySQL version 5.0.22+, 5.1.12+ or any version of 5.5 as long as you are using the most recent build of Sphinx. You must compile MySQL with the Sphinx files though, so you might as well go ahead and grab the latest version of MySQL while you’re at it.
Once you have MySQL up and running with SphinxSE, you can create the necessary table and run queries against it. An example query would look like this:
mysql> SELECT * FROM search WHERE query = 'search term';
query field is a magic field that takes all the conditions for the entire query, separated by semi-colons. For instance if you want to sort by a specific field, you would use:
mysql> SELECT * FROM search WHERE query = 'search term;sort=attr_desc:user_id';
The other option for querying the sphinx indexes is to use SphinxQL, which is Sphinx’s own query language. It closely mimics SQL. The great thing about it though, is that the Sphinx daemon actually accepts connections using the MySQL binary network protocol, which means that you can use the MySQL CLI to query sphinx, with no modifications necessary. You can also use the already-existing libraries that exist for languages to connect to MySQL servers, and point them at your Sphinx daemon and run the queries like that. For example:
<?php mysql_connect('localhost:9306'); $result = mysql_query('SELECT * FROM index WHERE MATCH('search term') ORDER BY user_id DESC'); mysql_close();
This will query the sphinx index and return a list of ids that you can then use to query against your permanent data store.