Sphinx - mejorar búsquedas full-text en MySQL

Sphinx es un motor de búsquedas full-text open-source.

Llegué a él gracias a la pobre performance de MySQL (gracias!), ya que en una tabla de 2 millones de registros, hacer una búsqueda por keywords tardaba 10seg. Este tiempo para una aplicación online no era nada bueno.

Luego de intentar mejorar la performance de MySQL me rendí y me puse a buscar. Me acordaba que existía Lucene, pero para Java (aunque debe existir algún port a PHP). De ahí fue que llegué a Sphinx e investigando un poquito parecía que servía. ¿Siguiente paso? probarlo obviamente, nunca hay que quedarse con lo que dicen los blogs, incluyendo este :)

Los siguientes pasos los probé en un ubuntu dapper, pero debería ser similar para otras distribuciones.

Primero instalar dependecias para compilar Sphinx

$ sudo apt-get install build-essential libmysql++-dev

Bajar el .tar.gz y extraer:

$ tar -xf sphinx-0.9.8-rc2.tar.gz

Compilar

$ cd sphinx-0.9.8-rc2
$ sh ./configure
$ make
$ sudo make install

Listo. Sphinx está instalado, ahora a configurarlo.
Para este ejemplo (muy simple) sólo utilizaremos una tabla, que contiene entradas de un blog. Así sería la tabla:

CREATE TABLE Post (
id int(10) unsigned NOT NULL auto_increment,
titulo varchar(7) NOT NULL,
post varchar(256) NOT NULL,
PRIMARY KEY (id)
) ;

Creamos el archivo de configuración

$ sudo gedit /usr/local/etc/sphinx.conf

Primeramente vamos a crear una fuente de datos, en este caso MySQL pero tambíen soporta PostgreSQL, XML y otras hierbas.

source blog
{
type                            = mysql
sql_host                        = localhost
sql_user                        = blog_user
sql_pass                        = passwd
sql_db                          = blog

# indexer query
# document_id MUST be the very first field
# document_id MUST be positive (non-zero, non-negative)
# document_id MUST fit into 32 bits
# document_id MUST be unique

sql_query                       = \
      SELECT \
              id, titulo, post \
      FROM \
              Post;

# document info query
# ONLY used by search utility to display document information
# MUST be able to fetch document info by its id, therefore
# MUST contain '$id' macro
# Esta parte se utiliza para devolver los resultados de la busqueda
# a partir de la consulta. Por cada resultado encontrado, realiza esa
# consulta y retorna los datos de la misma.
sql_query_info          = SELECT * FROM Post WHERE id=$id
}

Luego agregamos la definición de nuestro índice:

index posts
{
source                  = blog
# ruta donde se guarda el indice
path                    = /var/data/sphinx/catalog

# tamaño minimo de palabra para indexar
min_word_len            = 3

}

Finalmente agregamos los datos para el demonio de sphinx (que vamos a necesitar cuando usemos algun API)

searchd
{
port    = 3312
log     = /var/log/searchd/searchd.log
query_log   = /var/log/searchd/query.log
pid_file   = /var/log/searchd/searchd.pid
}

Guardamos el archivo y creamos la carpeta para guardar los índices:

$ sudo mkdir -p /var/data/sphinx

Creamos el índice:

$ sudo /usr/local/bin/indexer --config /usr/local/etc/sphinx.conf --all

Probamos la búsqueda, en este ejemplo buscamos la palabra "Java":

$ /usr/local/bin/search --config /usr/local/etc/sphinx.conf Java

Esto sólamente es un ejemplo muy simple de lo que se puede hacer con Sphinx, para mas detalles de esta mágica aplicación, incluyendo integración con PHP o Rails: http://www.sphinxsearch.com/

¿Alguna otra experiencia con búsquedas fulltext?