Búsquedas FULL TEXT con Sphinx y MySQL

Hasta que tengamos en las manos la versión 5.6 de MySQL, si queremos hacer uso de los índices FULL TEXT solo podemos guardas nuestros datos en tablas MyISAM. Por lo tanto, tenemos que dejar de lado consistencia de datos, rápido chequeo de tablas, transacciones, etc. Y aunque realmente no necesitemos todas esas funcionalidades y podamos vivir solo con MyISAM, llegará un momento en el que los índices sean tan grandes que hacer uso de búsquedas de texto contra MySQL se terminará volviendo un cuello de botella. No nos vamos a engañar, el rendimiento de los FULL TEXT en MySQL no es bueno :)

Para ayudarnos en las búsquedas de texto dentro de nuestra aplicación web vamos a aprender a usar, a nivel básico, el servicio de búsquedas FULL TEXT Sphinx. Este servicio nos proveerá de un servicio de búsquedas rápido y escalable, independientemente del origen de los datos que pueden ser:

• MySQL
• PostgreSQL
• Oracle
• Ficheros XML
• etc.

Según los datos de la propia página de Sphinx, existen instalaciones que responden 200.000.000 de consultas al día con solo 15 servidores, lo que da un ejemplo de su potencia.

En una serie de posts iremos viendo como implementar y hacer uso de este sistema de búsquedas. Para ello, en un primer lugar tendremos dos máquinas con estos datos:

• perconaserver1 (192.168.100.10) con la base de datos employees en InnoDB. PerconaServer 5.5
• sphinx1 (192.168.100.1). Sphinx 0.99

Lo que vamos a terminar montando es un sistema de búsquedas mediante índices FULL TEXT que nos permita búscar en la base de datos de empleados, ya sea por nombre, apellido, departamento, fecha de contratación y todo lo que se nos ocurra. Todo esto sin necesidad de acceder a MySQL y con un incremento de velocidad realmente impresionante.

Doy por supuesto que todos sabemos instalar MySQL, crear usuarios e importar bases de datos. La base de datos que importaremos se puede descargar desde https://launchpad.net/test-db

Por lo tanto, comenzaremos con la configuración de Sphinx:

apt-get install sphinxsearch

El fichero de configuración se queda en /etc/sphinxsearch/sphinx.conf. El que viene por defecto tiene muchos comentarios, por lo que podemos usarlo como guia de referencia rápida. Para nuestro caso muchas de las opciones que vienen nos sobran, por lo que vamos a renombrarlo a sphinx.conf.old y creamos uno nuevo vacio.

El fichero de configuración se divide en secciones. Básicamente tenemos que indicarle de donde vamos a coger los datos a indexar, nombre y configuraciones de nuestro índice y servicio de búsqueda (searchd). Comenzamos.

source perconaserver1 
{
    type                    = mysql
    sql_host                = 192.168.100.10 
    sql_user                = sphinx
    sql_pass                = sph1nX
    sql_db                  = employees
    sql_query               = \
        select employees.emp_no, employees.first_name, employees.last_name, 
UNIX_TIMESTAMP(employees.hire_date) as hire_ts, salaries.salary, departments.dept_name
 from employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no LEFT JOIN 
dept_emp ON salaries.emp_no = dept_emp.emp_no LEFT JOIN departments ON 
dept_emp.dept_no = departments.dept_no group by employees.emp_no
    sql_attr_uint           = salary
    sql_attr_timestamp      = hire_ts
}

Aquí indicamos nuestro primer origen de datos, que se llamará percona1. Como podemos ver, será de tipo MySQL y le indicamos IP, usuario, contraseña y base de datos a la cual conectarse. Para recoger los datos le indicamos una query a ejecutar, que básicamente lo que hace es recoger todos los datos del empleado junto con su salario y nombre del departamento en el que trabaja. Aquí se puede escribir la query que cada uno desee. Los datos que devuelva la query será los que indexará Sphinx.

A la hora de crear la query solamente hay que tener en cuenta una cosa. El primer valor que devuelva nuestra query debe ser de tipo UNIQUE, por lo que generalmente se tratará de la Primary Key. En este caso, el campo UNIQUE será el número único identificador del trabajador, y por lo tanto, una Primary Key.

En las dos últimas líneas estamos creando dos atributos que podremos usar para afinar nuestras búsquedas. En este caso creamos un atributo de tipo "Unsigned Int" donde guardaremos el salarios y otro de tipo "Timestamp" donde guardaremos la fecha de contratación. Estos dos atributos se usarán para delimintar los resultados. Por ejemplo, podemos decir que queremos todos los empleados que se llamen "Juan" y que cobren más de 100.000. "Juan" será una búsqueda Full Text, mientras que el atributo "salary" se usará para mostrar únicamente aquellos "Juan" que cobren más de dicha cifra. Todas las demás columnas que no definamos como atributo se guardarán como índices FULL TEXT. En este caso, todo lo que no sea salario o fecha de contratación será un FULL TEXT.

index employees 
{
    source          = perconaserver1
    path            = /var/lib/sphinxsearch/data/employees
    min_word_len        = 1
    charset_type        = sbcs
}

Aquí indicamos la creación de un índice con nombre "employees" cuyo origen de datos está en perconaserver1. A parte de eso, indicamos la ruta donde se guardarán los ficheros, el tamaño mínimo de las palabras a indexar y el tipo de charset utilizado.

searchd
{
    log                 = /var/log/sphinxsearch/searchd.log
    query_log           = /var/log/sphinxsearch/query.log
    read_timeout        = 5
    client_timeout      = 300
    max_children        = 30
    pid_file            = /var/run/searchd.pid
    max_matches         = 1000
    seamless_rotate     = 1
    preopen_indexes     = 0
    unlink_old          = 1
    mva_updates_pool    = 1M
    max_packet_size     = 8M
    max_filters         = 256
    max_filter_values   = 4096
    listen = localhost:9312
    listen = localhost:9306:mysql41
}

Estas son las opciones del servicio de búsquedas, al cual le haremos las peticiones para que este busque en el índice y nos devuelva los resultados buscados. En este caso, las opciones a recalcar son:

• max_children: son el número máximo de procesos que se podrán lanzar para atender peticiones en paralelo
• client_timeout: tiempo máximo que esperará entre peticiones antes de cerrar la conexión
• max_matches: número máximo de resultados que guardará en RAM y que devolverá al cliente
• listen: aquí le indicamos en que IP y puerto escuchar las peticiones de búsqueda. En la primera línea escuchará la API de searchd, que será contra la cual nos tendremos que conectar mediante PHP, Ruby, Python o el lenguaje que más rabia nos de. Mientras que en la segunda línea le indicamos que escuche en el puerto 9306 y use el protocolo de red de MySQL. Gracias a este último nos podremos conectar a searchd con un cliente de mysql normal y corriente y lanzar peticiones de búsquedas con los SELECT de toda la vida.

Una vez terminada la configuración debemos ejecutar el indexador para que lea todos los datos de la base de datos y cree el índice. Desde la consola ejecutamos:

root@sphinx1:~# indexer --all
Sphinx 0.9.9-release (r2117)
Copyright (c) 2001-2009, Andrew Aksyonoff
using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'employees'...
collected 300024 docs, 7.0 MB
sorted 0.9 Mhits, 100.0% done
total 300024 docs, 7036568 bytes
total 11.790 sec, 596822 bytes/sec, 25447.19 docs/sec
total 4 reads, 0.004 sec, 2694.5 kb/call avg, 1.2 msec/call avg
total 24 writes, 0.028 sec, 1014.5 kb/call avg, 1.1 msec/call avg

Listo, índice creado. Ya podemos lanzar el servicio de Sphinx y lanzarle peticiones:

root@sphinx1:~# /etc/init.d/sphinxsearch start

Para lanzar una búsqueda nos podemos conectar usando un cliente de MySQL:

root@sphinx1:~# mysql -h 127.0.0.1 -P 9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-release (r2117)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

Aunque lo pueda parecer, no estamos conectados a la base de datos MySQL. Todas las consultas SELECT que lancemos las resolverá Sphinx sin llegar a conectarse a la base de datos final. Vamos a lanzar una query de ejemplo:

Queremos saber todos los trabajadores de nuestra empresa que se llamen Jongsuk o Saniya o Dekang, que NO trabajen en el departamento de producción que que su salario sea superior a los 85000 euros.

mysql> select * from employees WHERE MATCH('(Jongsuk|Saniya|Dekang)-Production') 
AND salary > 85000 ORDER BY salary ASC;
+--------+--------+-----------+--------+
| id     | weight | hire_ts   | salary |
+--------+--------+-----------+--------+
| 461700 |   1532 | 508287600 |  85090 |
| 236341 |   1531 | 522885600 |  85728 |
| 434733 |   1532 | 561855600 |  86368 |
| 204028 |   1532 | 559000800 |  86506 |
|  88618 |   1532 | 524959200 |  86608 |
|  87515 |   1531 | 541033200 |  87103 |
| 491721 |   1531 | 611704800 |  87360 |
|  54136 |   1531 | 669250800 |  87459 |
|  13564 |   1531 | 857343600 |  87914 |
|  82319 |   1531 | 624409200 |  88094 |
| 209948 |   1532 | 646005600 |  89143 |
| 417533 |   1531 | 709164000 |  90811 |
| 499794 |   1531 | 699404400 |  91982 |
| 425202 |   1531 | 627951600 |  95582 |
| 446559 |   1532 | 520380000 |  98285 |
| 279372 |   1531 | 659746800 |  99881 |
|  79903 |   1532 | 693702000 | 100803 |
+--------+--------+-----------+--------+
17 rows in set (0.00 sec)

¿Que vemos aquí?

• ID: es el identificador de nuestro trabajador, que identifica única y exclusivamente a un único trabajador. Es el ID del que hablamos anteriormente y que obligatoriamente tiene que ser UNIQUE y ser el primer resultado de nuestra query. En este caso, concuerda con la Primary Key de la tabla employees.
• hire_ts: es el timestamp de la fecha de contratación.
• salary: su sueldo.
• weight: el peso o importancia del resultado

Como vemos, no podemos saber nombres, apellidos o departamento. Esto es así porque los índices FULL TEXT guardan solamente eso, un índice, pero no su contenido. Pensad en el como en el índice de un libro. Por otro lado, como hire_ts y salary eran atributos y no FULL TEXT, si podemos ver sus valores. Ahora que tenemos el ID del trabajador podemos hacer una consulta a MySQL y sacar todos los datos de dicho trabajador. Como esto será una consulta contra la Primary Key, la respuesta será instantanea. Todo el trabajo de búsqueda ya lo ha hecho Sphinx, así que ahora el trabajo fácil queda para MySQL :)

Aquí la consulta al servidor de MySQL:

root@perconaserver1:~# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.5.13-55 Percona Server (GPL), Release 20.4
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use employees;
Database changed
mysql> select * from employees where emp_no=461700;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 461700 | 1960-06-17 | Dekang     | Anger     | M      | 1986-02-09 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)