Usar FULLTEXT en MySQL

En este artículo voy a explicar como se puede hacer un buscador basado en MySQL y PHP.

Como punto de partida, la información a buscar la tenemos que tener almacenada en una tabla de nuestra base de datos. Pongamos como ejemplo una tabla con artículos técnicos que llamaremos ARTICULOS. Esta tabla ARTICULOS tendrá como mínimo dos campos llamados TITULO y DESARROLLO, que almacenarán el título del artículo y su contenido respectivamente.

Los tipos de los campos a buscar deberán ser CHAR, VARCHAR o TEXT con sus variantes.

Ahora diseñaremos una página con un formulario y una caja de texto para escribir la cadena de consulta busqueda y llamar al script de búsqueda buscar.php. Ejemplo:

<FORM METHOD=POST ACTION="buscar.php">
  Buscar: <INPUT TYPE="text" NAME="busqueda">
</FORM>

En el fichero buscar.php es donde está el click de la cuestión.

Hay que hacer la consulta que busque las palabras y devuelva como resultado los registros coincidentes. Hasta aquí parece fácil y podríamos resolverlo así:

SELECT * FROM ARTICULOS WHERE DESARROLLO LIKE '%$busqueda%' OR TITULO LIKE '%$busqueda%'

Pues esta consulta nos mostrará todos los artículos que en su titulo o en su contenido aparezca la frase de búsqueda tal y como nosotros la introducimos. Esto es muy limitado ya que un cambio en el orden de las palabras o un artículo de separación dará al traste con nuestra búsqueda no mostrando los resultados deseados.

Si bien podríamos depurar y mejorar la sintaxis de nuestra búsqueda utilizando el operador LIKE las búsquedas resultarían muy lentas y no tendrían el resultado esperado.

La solución mas eficiente es utilizar los índices FULLTEXT específicamente indicados para estos menesteres. Pues bien esto implica ir a nuestra base de datos, y crear un indice FULLTEXT con todos los campos que deseamos incluir en nuestra busqueda, que en nuestro caso son TITULO y DESARROLLO. Para ello basta con el phpmyadmin o bien escribir la instrucción directamente:

   ALTER TABLE ARTICULOS ADD FULLTEXT(TITULO, DESARROLLO);

Una vez creado el índice la instrucción SQL para buscar será:

SELECT * FROM ARTICULOS WHERE MATCH(TITULO, DESARROLLO) AGAINST ('$busqueda')

Esta línea utiliza la función MATCH ... AGAINST ... que encuentra el texto buscado, usando consultas en lenguaje natural parecido a como lo hacen los motores de búsqueda. Además, se calcula internamente una puntuación en función de como aparecen los términos buscados dentro de nuestro artículo.

Perfeccionando la búsqueda:

SELECT  * , MATCH (TITULO,DESARROLLO) AGAINST ('$busqueda') AS puntuacion
  FROM ARTICULOS WHERE  MATCH (TITULO, DESARROLLO) AGAINST ('$busqueda')
  ORDER  BY puntuacion DESC LIMIT 50

Esta línea devuelve los 50 primeros resultados encontrados ordenados de mas a menos puntuación. El valor de la puntuacion es un número decimal comprendido entre 0 y 1 por cada ocurrencia del patrón de búsqueda, que se irá sumando si ese patrón es encontrado en varias ocasiones. De cualquier forma esta puntuación es un algoritmo interno de la base de datos.

Algunos problemas. Las búsquedas realizadas con MATCH ...AGAINST en ocasiones fallan cuando el término a buscar contiene una sola palabra. Por el contrario son unas búsquedas rapidísimas que producen mejores resultados que otros métodos mas rudimentarios como el LIKE cuando se trata de varias palabras o frases.

La solución que le he dado a ese problema de las búsquedas con MATCH...AGAINST has sido chequear el número de palabras a buscar, utilizando una búsqueda simple con LIKE en el caso de una sola palabra, y el método MATCH...AGAINST en el caso de varias.

Ejemplo del fichero buscar.php

<?php
//cadena de conexion
mysql_connect("host","usuario","password");
// DEBO PREPARAR LOS TEXTOS QUE VOY A BUSCAR si la cadena existe
if ($busqueda<>''){
  //CUENTA EL NUMERO DE PALABRAS
  $trozos=explode(" ",$busqueda);
  $numero=count($trozos);
  if ($numero==1) {
    //SI SOLO HAY UNA PALABRA DE BUSQUEDA SE ESTABLECE UNA INSTRUCION CON LIKE
    $cadbusca="SELECT  REFERENCIA, TITULO FROM ARTICULOS WHERE VISIBLE =1
      AND DESARROLLO LIKE  '%$busqueda%' OR TITULO LIKE  '%$busqueda%' LIMIT 50";
  } elseif ($numero>1) {
    //SI HAY UNA FRASE SE UTILIZA EL ALGORTIMO DE BUSQUEDA AVANZADO DE MATCH AGAINST
    //busqueda de frases con mas de una palabra y un algoritmo especializado
    $cadbusca="SELECT  REFERENCIA, TITULO, MATCH ( TITULO, DESARROLLO )
      AGAINST (  '$busqueda' ) AS Score FROM ARTICULOS WHERE
      MATCH ( TITULO, DESARROLLO ) AGAINST (  '$busqueda' ) ORDER  BY Score DESC LIMIT 50";
  }
  $result=mysql("teleformacion", $cadbusca);
  While($row=mysql_fetch_object($result))
  {
    //Mostramos los titulos de los articulos o lo que deseemos...
    $referencia=$row->REFERENCIA;
    $titulo=$row->TITULO;
    echo $referencia." - ".$titulo."<br>";
  }
}
?>

Muchas veces los periodicos y webs similares tienen algo como “More stories like this” para empujar a los lectores a articulos parecidos. Esta opción le añade bastante calidad a un web site. Estos links deberían ser cercanos pero no redundantes.

En el ejemplo que muestro a continuación en la base de datos hay un title, un summary, y el contenido del artículo. Primero, necesitamos decidir qué contituye un artículo “relacionado”. Hemos decididio que deberían ser las 10 más usadas palabras del sumario y las 20 más usadas del contenido.

$titlewords = explode(“ “, $title);
$words = explode(“ “, $summary);
$temparray = array();
foreach ($words as $word) {
$temparray[$word]++;
}
$findwords = array_chunk(arsort($temparray), 10);
$summarywords = array_keys($findwords[0]);
 $words = explode(“ “, $content);
$temparray = array();
foreach ($words as $word) {
$temparray[$word]++;
}
$findwords = array_chunk(arsort($temparray), 20);
$contentwords = array_keys($findwords[0]);

Ya que estos resultados muchos FULLTEXT-con palabras absurdas como “the”, “of”, “that”, y “this”, se recomienda usar un array para excluir palabras y comparar cada palabra contra al base de datos de exclusión antes de añadirla al array final.

Puede parecer que enviar una consulta con un FULLTEXT de 20 palabras puede ser lento pero mi experiencia me dice que va bien. Especialmente cuando lo comparas con escribir tu propio script PHP para comparar cada palabra y decidir sobre su relevancia.

Las consultas las he dejado separadas para dar mayor claridad pero quizás sería mejor juntarlas de esa forma evitas que se cargue demasiado la consulta al realizar la petición a la web y al MySQL.

$titlequery = implode(“ “, $titlewords);
$query1 = mysql_query(“SELECT StoryID, MATCH(Title) AGAINST ('”.$titlequery.”') as Match FROM Articles WHERE MATCH(Title) AGAINST ('”.$titlequery.”')”, $mysqldb);
$count = mysql_num_rows($query1);

foreach($i=0;$i<$count;$i++) {
$searchresults[mysql_result($query1, $i, "StoryID")] += (mysql_result($query1, $i, "Match")*3);
}

$summaryquery = implode(“ “, $summarywords);
$query2 = mysql_query(“SELECT StoryID, MATCH(Summary) AGAINST ('”.$summaryquery.”') as Match FROM Articles WHERE MATCH(Summary) AGAINST ('”.$summaryquery.”')”, $mysqldb);
$count = mysql_num_rows($query2);
foreach($i=0;$i<$count;$i++) {
$searchresults[mysql_result($query2, $i, "StoryID")] += (mysql_result($query2, $i, "Match")*2);
}

$contentquery = implode(“ “, $contentwords);
$query3 = mysql_query(“SELECT StoryID, MATCH(Content) AGAINST ('”.$contentquery.”') as Match FROM Articles WHERE MATCH(Content) AGAINST ('”.$contentquery.”')”, $mysqldb);
$count = mysql_num_rows($query3);
foreach($i=0;$i<$count;$i++) {
$searchresults[mysql_result($query3, $i, "StoryID")] += mysql_result($query3, $i, "Match");
}

En este caso, los resultados por palabras en el título se multiplican por 3, las del sumario se multiplican por 2, y los comentarios se dejan como están. Esto da contenido a los articulos con titulos o resúmenes similares y aquellos que se parecen en el contenido. El uso de multiplicadores permite significar en mayor o menos medida las consultas relevantes que coincidan con lo buscado. El array $searchresults se puede ordenar por valor, y los top 5 o 10 se pueden mostrar en la web.

La función FULLTEXT de MySQL se puede usar desde dentro de PHP para ver cómo se acerca una consulta o encontrar articulos semejantes en la Base de Datos. Debido a que el propósito del programa no es buscar terminos exactos sino que se acerquen al termino buscado la función FULLTEXT es ideal para eso.