Hoy veremos como utilizar Sphinx Search Engine en PHP para agilizar muchísimo las búsquedas en portales con mucho trafico.
¿Que hace Sphinx?
Sphinx es un motor de búsqueda que se encarga de indexar los resultados de una consulta a una base de datos y los mete en un archivo binario. De esta forma al utilizar Sphinx las búsquedas no se hacen sobre la base de datos sino sobre el indice de Sphinx, así Sphinx nos devuelve los identificadores de cada registro muy rápidamente desde sus ficheros binarios y nos evitamos que MySQL compruebe las coincidencias fila por fila consumiendo muchos recursos. Sphinx nos devolverá los identificadores y nosotros haremos una consulta por cada identificador para conseguir sus datos, en teoría será mucho mas rápido que hacer la consulta compleja sobre la base de datos.
Podemos descargar el API de este enlace https://github.com/gigablah/sphinxphp o en Windows utilizar el API en PHP que incluye.
Base de datos
Creamos la base de datos
CREATE DATABASE sphinx_blog; use sphinx_blog; CREATE TABLE categories( id bigint not null auto_increment, name varchar(255), constraint pk_id_category primary key(id) )Engine=InnoDb; INSERT INTO categories VALUES(NULL,"PHP"); INSERT INTO categories VALUES(NULL,"Laravel"); INSERT INTO categories VALUES(NULL,"CSS3"); INSERT INTO categories VALUES(NULL,"MySQL"); CREATE TABLE users( id bigint not null auto_increment, role varchar(255), name varchar(255), surname varchar(255), description varchar(255), email varchar(255), password varchar(255), image varchar(255), date date, time time, constraint pk_id_course primary key(id) )Engine=InnoDb; INSERT INTO users VALUES(NULL, "user", 'Víctor', 'Robles', 'Puedes conocerme mejor en http://victorroblesweb.es', 'victor@victor.es', 'secret', 'default.png', CURDATE(), CURTIME()); INSERT INTO users VALUES(NULL, "user", 'Jose', 'Sánchez', 'Hola que tal soy programador', 'jose@victorroblesweb.es', 'secret', 'default.png', CURDATE(), CURTIME()); INSERT INTO users VALUES(NULL, "user", 'David', 'Lopez', 'Escribo codigos como sonetos de Shakespeare', 'daviv@victorrobles.es', 'secret', 'default.png', CURDATE(), CURTIME()); CREATE TABLE posts( id bigint not null auto_increment, user_id bigint, category_id bigint, title varchar(255), description varchar(255), image varchar(255), content varchar(255), status varchar(50), date date, time time, constraint pk_id_post primary key(id), constraint fk_category_post foreign key(category_id) references categories(id), constraint fk_user_post foreign key(user_id) references users(id) )Engine=InnoDb; INSERT INTO posts VALUES(NULL, 1, 1, "Primera publicación", "Esta es la primera publicación del blog", "image-1.png", "Contenido de la primera publicación","active",CURDATE(),CURTIME()); INSERT INTO posts VALUES(NULL, 1, 2,"Segunda publicación", "Esta es la segunta publicación del blog", "image-1.png", "Contenido de la segunda publicación","active",CURDATE(),CURTIME()); INSERT INTO posts VALUES(NULL, 2, 3,"Tercera publicación", "Esta es la tercera publicación del blog", "image-1.png", "Contenido de la tercera publicación","active",CURDATE(),CURTIME()); INSERT INTO posts VALUES(NULL, 2, 2,"Cuarta publicación", "Esta es la cuarta publicación del blog", "image-1.png", "Contenido de la cuarta publicación","active",CURDATE(),CURTIME()); INSERT INTO posts VALUES(NULL, 3, 1,"Quinta publicación", "Esta es la quinta publicación del blog", "image-1.png", "Contenido de la quinta publicación","active",CURDATE(),CURTIME()); INSERT INTO posts VALUES(NULL, 3, 3,"Sexta publicación", "Esta es la sexta publicación del blog", "image-1.png", "Contenido de la sexta publicación","active",CURDATE(),CURTIME()); CREATE TABLE tags( id bigint not null auto_increment, name varchar(255), constraint pk_id_tag primary key(id) )Engine=InnoDb; INSERT INTO tags VALUES(NULL,"desarrollo web"); INSERT INTO tags VALUES(NULL,"php"); INSERT INTO tags VALUES(NULL,"orm"); INSERT INTO tags VALUES(NULL,"poo"); INSERT INTO tags VALUES(NULL,"symfony"); INSERT INTO tags VALUES(NULL,"desarollo movil"); CREATE TABLE tags_posts( id bigint not null auto_increment, tag_id bigint, post_id bigint, constraint pk_id_tag_post primary key(id), constraint fk_tag_id_tp foreign key(tag_id) references tags(id), constraint fk_post_id_tp foreign key(post_id) references posts(id) )Engine=InnoDb; INSERT INTO tags_posts VALUES(NULL, 1, 1); INSERT INTO tags_posts VALUES(NULL, 2, 1); INSERT INTO tags_posts VALUES(NULL, 3, 1); INSERT INTO tags_posts VALUES(NULL, 3, 2); INSERT INTO tags_posts VALUES(NULL, 4, 2); INSERT INTO tags_posts VALUES(NULL, 5, 2); INSERT INTO tags_posts VALUES(NULL, 6, 3); INSERT INTO tags_posts VALUES(NULL, 1, 3); INSERT INTO tags_posts VALUES(NULL, 3, 4); INSERT INTO tags_posts VALUES(NULL, 5, 5); INSERT INTO tags_posts VALUES(NULL, 6, 6);
Configuración de Sphinx
Configuramos sphinx.conf para las busquedas, añadimos un nuevo source y un nuevo index con la consulta correspondiente.
source proyecto
{
type = mysql
sql_host = localhost
sql_user = root
sql_pass =
sql_db = sphinx_blog
sql_port = 3306 # optional, default is 3306
sql_query = \
select p.id, p.user_id as 'user_id', CONCAT(u.name," ",u.surname) as 'user', c.name as 'category', p.category_id as 'category_id', c.name, p.title, p.description, p.date, p.time \
from posts p INNER JOIN categories c ON c.id=p.category_id INNER JOIN users u ON u.id=p.user_id
sql_attr_uint = category_id
sql_attr_uint = user_id
sql_attr_multi = uint tag from query; \
select p.id, pt.tag_id as tag from posts as p, tags_posts as pt where p.id = pt.post_id;
}
index proyecto1
{
source = proyecto
path = C:/sphinx/data/proyecto1
}
index testrt
{
type = rt
rt_mem_limit = 128M
path = C:/sphinx/data/testrt
rt_field = title
rt_field = content
rt_attr_uint = gid
}
indexer
{
mem_limit = 128M
}
searchd
{
listen = 9312
listen = 9306:mysql41
log = C:/sphinx/log/searchd.log
query_log = C:/sphinx/log/query.log
read_timeout = 5
max_children = 30
pid_file = C:/sphinx/log/searchd.pid
seamless_rotate = 1
preopen_indexes = 1
unlink_old = 1
workers = threads # for RT to work
binlog_path = C:/sphinx/data
compat_sphinxql_magics = 0
}
Búsqueda básica con Sphinx en PHP
A continuación muestro un ejemplo sencillo de como utilizar Sphinx, se pueden poner muchos filtros y muchas cosas, que no se muestran aquí.
<?php
require_once 'sphinxapi.php';
$conexion=new mysqli('localhost','root','','sphinx_blog');
$cl = new SphinxClient();
$cl->SetServer( "localhost", 9312 );
$cl->SetMatchMode( SPH_MATCH_ANY );
// el primer parámetro es la query, es lo que queremos buscar: cumpleaños
// el segundo parámetro es el index que vamos a usar para buscarlo
$result = $cl->Query( $_GET["q"], 'proyecto1' );
if ( $result === false ) {
echo "fallo en Query: " . $cl->GetLastError() . ".n";
}
else {
if ( $cl->GetLastWarning() ) {
echo "WARNING: " . $cl->GetLastWarning() . "
";
}
if ( ! empty($result["matches"]) ) {
foreach ( $result["matches"] as $doc => $docinfo ) {
$post=$conexion->query("select p.id, p.user_id as 'user_id', CONCAT(u.name,' ',u.surname) as 'user', c.name as 'category', p.category_id as 'category_id', c.name, p.title, p.description, p.date, p.time
from posts p INNER JOIN categories c ON c.id=p.category_id INNER JOIN users u ON u.id=p.user_id
WHERE p.id=$doc ");
$posts[] = $post->fetch_assoc();
}
}
}
foreach($posts as $post){
echo $post["title"];
echo "<br/>";
echo $post["category"];
echo "<br/>";
echo $post["user"];
echo "<hr/>";
}
?>
Más información:
Sphinx Search Sitio oficial
