Strait Fram Singapuur

This is an extension of my earlier blog post about performing free text search with Sphinx (v2.1.6+). This time we are going to access the index via JDBC as opposed to Sphinx API and perform some basic queries to fetch indexed data.

Add following entries to searchd section inside sphinx.conf

listen=9306:mysql41
mysql_version_string=5.0.37

Once done start the sphinx daemon (searchd) so that the search engine can be accessed from external applications on port 9306. Next just get the JDBC connection (no need to specify database details and credentials) as shown below and you are ready to rock.

Class.forName("com.mysql.jdbc.Driver");
con = DriverManager
 .getConnection("jdbc:mysql://localhost:9306");
statement = con.createStatement();
// perform wild search on last names
resultSet = statement
 .executeQuery("select * from addressBookIndex 
where MATCH ('gogna | sharma')"); 

Note:

• 1. The attributes specified in the sphinx.conf file (e.g. sql_attr_string, sql_attr_uint etc) can not be used with MATCH though they can be part of WHERE clause.
• 2. Given that now String attributes are supported in order to avoid making a database call define key columns as attributes as well as fields in sql_query.
• 3. Results from Delta Indexes can be handled using union.
• 4. Explore RT indexes for real time update scenarios.

For more information about the Sphinx QL syntax visit here.