First read this... then this

   1  draft

   1  source feed_items
   2  {
   3          type                    = mysql
   4  
   5          sql_host                = 127.0.0.1
   6          sql_user                = root
   7          sql_pass                =
   8          sql_db                  = xxx_production
   9          sql_port                = 3306  # optional, default is 3306
  10          sql_sock                = /var/run/mysqld/mysqld.sock
  11  
  12          sql_query_pre           = SET NAMES utf8
  13          #sql_query_pre          = SET SESSION query_cache_type=OFF
  14  
  15  	# Unique ID should be first column
  16          sql_query               = \
  17                  SELECT i.id, i.title, i.link, f.link, f.title FROM feed_items i LEFT JOIN feeds f ON f.id = i.feed_id
  18  }
  19  
  20  
  21  index feed_items
  22  {
  23          source                  = feed_items
  24          path                    = /var/sphinx/xxx
  25          morphology              = libstemmer_sv
  26          charset_type            = utf-8
  27  }
  28  
  29  
  30  indexer
  31  {
  32          mem_limit               = 32M
  33  }
  34  
  35  searchd
  36  {
  37          address                 = 127.0.0.1
  38          port                    = 3312
  39          log                     = /var/log/sphinx/searchd.log
  40          query_log               = /var/log/sphinx/query.log
  41          pid_file                = /var/log/searchd.pid
  42          max_matches             = 1000
  43  }
  44  

Inspiration for this snippet was taken from this post on the Sphinx forum, plus this blog post.

Compiling Sphinx

First install the prerequisites:

   1  sudo aptitude install libmysql++-dev libmysqlclient15-dev checkinstall

Next download sphinx, libstemmer and install everything and the fish:

   1  cd /usr/local/src
   2  
   3  wget http://sphinxsearch.com/downloads/sphinx-0.9.8-rc2.tar.gz
   4  tar zxvf sphinx-0.9.8-rc2.tar.gz 
   5  
   6  cd sphinx-0.9.8-rc2/
   7  
   8  # Add stemming support for Swedish, Finnish and other fun languages.
   9  wget http://snowball.tartarus.org/dist/libstemmer_c.tgz
  10  tar zxvf libstemmer_c.tgz
  11  
  12  ./configure --with-libstemmer
  13  make
  14  
  15  make install

Configure Sphinx

Create a sphinx.conf file in your Rails config directory, as described here, or use this template.

Install acts_as_sphinx plugin

   1  ./script/plugin install http://svn.datanoise.com/acts_as_sphinx

Add acts_as_sphinx to your model:

   1  class Documents
   2     acts_as_sphinx
   3  end

Indexing content

   1  rake sphinx:index
   2  
   3  (in /var/www/xxx.com/releases/20080429144230)
   4  Sphinx 0.9.8-rc2 (r1234)
   5  Copyright (c) 2001-2008, Andrew Aksyonoff
   6  
   7  using config file './sphinx.conf'...
   8  indexing index 'xxx.com'...
   9  collected 5077 docs, 0.6 MB
  10  sorted 0.1 Mhits, 100.0% done
  11  total 5077 docs, 632096 bytes
  12  total 0.160 sec, 3950427.25 bytes/sec, 31729.86 docs/sec

Reindexing content

sphinx:index shouldn’t be run while the searchd process is running, so use rake sphinx:rotate instead, which restarts the searchd process after indexing.

Starting the daemon

   1  mkdir -m 664 /var/log/sphinx
   2  rake sphinx:start
   3  
   4  (in /var/www/xxx.com/releases/20080429144230)
   5  Sphinx 0.9.8-rc2 (r1234)
   6  Copyright (c) 2001-2008, Andrew Aksyonoff
   7  
   8  using config file './sphinx.conf'...
   9  Sphinx searchd server started.

Searching

   1  Documents.find_with_sphinx 'why did I write this'