SphinxConnector.NET - Getting Started with Sphinx, MySQL and SphinxConnector.NET

Getting Started with Sphinx, MySQL and SphinxConnector.NET

This tutorial will guide you through setting up an index for Sphinx and accessing it from a .NET application using SphinxConnector.NET. The index will be created for the Sakila sample database provided by MySQL. To follow this tutorial you need to have a working installation of MySQL, Visual Studio 2005 or 2008 and, of course, SphinxConnector.NET.

Setting things up

If you haven't already downloaded and installed SphinxConnector.NET please do so now. Also download the Windows version of Sphinx 0.9.9 from the Sphinx site. Installing Sphinx is just a matter of unpacking the archive.

Next we need some data to search through. For this tutorial we'll use the Sakila database. Sakila is a sample database provided by MySQL which models a video rental store. We will use Sphinx and SphinxConnector.NET to do full-text searches through movie titles and descriptions, and filter movies based on attributes like rental rate or length. To import the data into your MySQL server, just unpack the archive and execute the two scripts against your MySQL server, e.g. via the MySQL command line client or a graphical client like MySQL Query Browser. After creating the database we can now index some data.

Creating the Index

SphinxConnector.NET already comes with a sample config file for the Sakila database. Go to the Samples directory of your SphinxConnector.NET installation and copy the file sphinx_099.conf to the appropriate directory of your Sphinx installation. On Windows this usually is the bin directory, where searchd.exe resides. Alternatively you can just copy the index and source sections from the sample config file into an existing Sphinx config file. Now adjust the following settings for your installation of MySQL:

sql_user: The (MySQL) user, the indexer should use to logon to server
sql_pass: The password of the user
sql_host: The IP address or the name of the host where the MySQL server resides
sql_port: The port of the MySQL server

Now open a command line/terminal window and switch to the directory where the indexer executable resides. To create the index type indexer.exe sakila and hit enter. The indexer will pull the data from the MySQL server and create the index files. You should now see output similar to this:

The index has been created and can now be accessed from SphinxConnector.NET. Now start the Sphinx search deamon by typing searchd and hitting enter. Note that in a production environement you'll usually want to run Sphinx as a service.

Accessing the index with SphinxConnector.NET

Start Visual Studio and create a new Console Application named SphinxConnectorDemo. Add a reference to Gronewold.SphinxConnector.dll to your project. Put the following code in the Main method of the Program.cs file:

1: SphinxClient sphinxClient = new SphinxClient();

2: SphinxSearchResult sphinxSearchResult = sphinxClient.Query("room", "sakila");

3:

4: foreach (SphinxMatch match in sphinxSearchResult.Matches)

5: {

6:   Console.WriteLine("DocumentId {0} Weight {1}", match.DocumentId, match.Weight);

7: }

8:

9: Console.ReadLine();

Example 1: Performing a simple Sphinx query in C#

You also need to add the following using directive:

using Gronewold.SphinxConnector;

What the code does is this: First, create an instance of the SphinxClient class, the is the main class used to access Sphinx. Next, we use the Query() method to search for documents which contain the word "room" in all fields that were setup for full-text search. In our example that is the title and the description field. We also specify that we only want to search through index with the name "sakila" (we've setup only one index, so the result would be the same if didn't specify any name). Query() returns an instance of the SphinxSearchResult class which hold information about the documents which match our query. Last, we just output the ids and the weight of the matched document.

If you hit F5 to start our little demo, you should see the console window outputting the following lines: DocumentId 52 Weight 1
DocumentId 87 Weight 1
DocumentId 743 Weight 1

In a real world application you wouldn't of course just output the document ids, but rather use them to fetch the real documents from the database.

Filtering Documents Based On Attribute Values

In many cases we don't just want to perform a full-text search, but rather filter our results based on other criteria. In our example video rental store, we might want to search only for films within a certain price range or only films with a certain actor. If you look at the configuration for our index, you'll see that we added quite a few attributes to the index, including the rental rate and the actors. We'll now setup filters based on these two attributes.

To specify a filter for a query, SphinxConnector.NET provides the methods SetFilter and SetFilterRange. We'll now add a filter on the rental rate attribute to our example:

sphinxClient.SearchOptions.SetFilterRange<Sphinxfloat>("rental_rate", 0, 0.5f);

Example 2: Setting a range filter on a Sphinx float attribute (C#)

Trying to be cheap, we do not want to pay more then 50 cents for a movie. Alas, if we run the search again, no results are returned. Seems that we have pay a bit more to rent a movie here, so we remove the filter. But, if we have pay more than 50 cents, at least our favorite actress Uma Wood should be in the movie. So let's set a filter on the actor id attribute. Looking at the actor table in the database, we learn that Uma's id is 13, so the filter looks like this:

sphinxClient.SearchOptions.SetFilter<SphinxInteger>("actor_id", 13);

Example 3: Setting a filter on a Sphinx uint attribute (C#)

If now execute the search with our filter we get exactly one result. Seems that we found the movie we'll be watching tonight.

Conclusion