En algún punto, cuando te encuentras diseñando la base de datos de tu proyecto, notarás que sus tablas pueden incluir información compuesta de una gran cantidad de texto, como por ejemplo, la descripción extensa de un producto o cualquier contenido que supere los 256 caracteres.
Los índices tradicionales tienen restricciones de longitud que no les permiten exceder esta capacidad, dando paso al uso de índices prefijos que recortan el contenido. Sin embargo, hay búsquedas que necesitan revisar obligatoriamente todo el contenido existente en las columnas de texto para encontrar una coincidencia, lo que se conoce como búsquedas de texto completo o full-text search en inglés.
Además, al ejecutar consultas de texto donde participa una cláusula WHERE ligeramente compleja, con expresiones lógicas formadas por varios AND, OR, LIKE u operadores de igualdad, el tiempo de respuesta puede llegar a ser muy lento si tuviéramos que recorrer todos los registros de una tabla grande. Afortunadamente para este problema también existe una solución: los índices FULLTEXT, lo cuales podemos emplear al realizar búsquedas de texto completo sobre campos CHAR, VARCHAR y TEXT.
MySQL posee diversos tamaños para datos de tipo texto: TINYTEXT, TEXT, MEDIUMTEXT y LONGTEXT. Puedes conocerlos revisando nuestro eBook Aprende SQL con MySQL y MariaDB disponible en Leanpub.
Imaginemos que queremos hacer un proyecto pequeño que nos permita revisar una documentación, probablemente usaríamos una tabla de base de datos que contenga el título del capítulo y su contenido. De hecho, hemos preparado un proyecto demo en Laravel 8 para ti en el siguiente repositorio.
Creando un índice FULLTEXT desde Laravel
Laravel no posee un método para crear un índice FULLTEXT, pero podemos hacerlo manualmente usando DB::statement de la siguiente manera:
// database/migrations/2020_12_14_055343_create_chapters_table.php
public function up()
{
Schema::create('chapters', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->longText('content');
$table->string('translator');
$table->timestamps();
});
DB::statement('
create fulltext index chapters_title_content_fulltext
on chapters(title, content);
');
}
Para crear un índice FULLTEXT puedes usar el comando CREATE FULLTEXT INDEX seguido del nombre del índice, la cláusula ON, el nombre de la tabla y los campos de donde se tomarán los valores para el índice.
En el ejemplo anterior, hemos creado el índice chapters_title_content_fulltext (siguiendo la convención de nombres de Laravel) con los campos title y content en la tabla chapters.
También puedes crear un índice FULLTEXT usando un solo campo, o quizá un índice compuesto por una combinación de varios de ellos. Pero en este último caso se define un solo índice y no se permite la creación de índices parciales internos como en la forma tradicional.
Funciones de búsquedas en el texto completo
La búsqueda de texto completo es ejecutada usando la sintaxis MATCH() ... AGAINST()
MATCH() permite especificar la lista de campos de acuerdo a la definición del índice FULLTEXT, mientras que AGAINST() permite indicar el string con la «frase» que será buscada en dicho índice y opcionalmente un modificador para el tipo de búsqueda que se va a realizar.
Un índice FULLTEXT es diferente de otros índices porque analiza el texto de los campos y los organiza de forma que lleva un registro por cada palabra, así como la cantidad de veces que aparece, en qué proporción, el registro que la contiene, el desplazamiento entre dos palabras consecutivas, etc.
El índice FULLTEXT descarta de su análisis palabras con menos de tres caracteres que son menos significativas, tales como artículos definidos, artículos indefinidos, conjunciones, pronombres interrogativos, etc.
Existen tres tipos de búsquedas que podemos realizar con estos índices: en lenguaje natural, en modo booleano y con expansión de la consulta.
Búsquedas de texto completo en lenguaje natural
Esta es la forma predeterminada cuando no se especifica un modificador y le corresponde: IN NATURAL LANGUAGE MODE.
MATCH() ejecuta una búsqueda en lenguaje natural, que es aquella donde las palabras de la «frase a buscar» deben aparecer dentro del texto en cualquier orden y cualquier número de veces para que exista una coincidencia.
La «frase a buscar» se descompone en palabras correctas, es decir, palabras que no se exceptúan y por tanto tienen relevancia.
La relevancia es un valor numérico que permite medir la similaridad de la «frase» con respecto a una determinada fila.
Aquí salta a la vista la primera diferencia con respecto a las comparaciones de cadenas, ya que cuando comparas dos cadenas con los operadores =, <>, >, <, >=, <=, LIKE obtienes un resultado true o false, mientras que con la función MATCH() recibes un valor de alta precisión numérica (números de punto flotante) como por ejemplo: 0.22764469683170319, 1.5219271183014.
En las búsquedas de texto completo, normalmente es preciso ordenar los resultados de la consulta por relevancia y en orden descendente para así mostrar como primer resultado el más relevante.
Asimismo, la «frase a buscar» puede tener una o más palabras. El analizador (parser) se encargará de descomponer esta frase en palabras y hará el cálculo de relevancia para la frase completa y las palabras que la conforman.
Ten en cuenta que el valor de relevancia tanto de la «frase» como de las «palabras que la conforman» no es un porcentaje, sino un valor de alta precisión numérica.
Creando consulta de búsqueda de texto completo en Laravel
Dentro de routes/web.php creamos una ruta basada en Closure para realizar la consulta, la cual posee el parámetro search con la «frase a buscar» que colocaremos en la función AGAINST():
use App\Models\Chapter;
Route::get('/', function () {
return Chapter::query()
->when(request('search'), function ($query, $search) {
$query->select('id', 'title', 'content')
->selectRaw('
match(title, content)
against(? in natural language mode) as score
', [$search])
->whereRaw('
match(title, content)
against(? in natural language mode) > 0.0000001
', [$search]);
})
->get();
});
Observa que agregamos en la cadena de consulta de la solicitud este parámetro de forma opcional, lo cual nos permitirá hacer distintas búsquedas sin que tengamos que cambiar el código.
La función MATCH() ... AGAINST() puede aparecer en distintos lugares de una instrucción SQL, pero casi siempre se usa en la cláusula WHERE para filtrar los resultados o en el SELECT cuando queremos mostrar la relevancia de la «frase».
En el ejemplo anterior, hemos agregado esta función en ambas cláusulas, dentro del SELECT como un campo llamado score y en el WHERE para filtrar los resultados. La consulta se realiza de forma condicional y depende de qué pasemos al parámetro search en la ruta creada.
Debes tener especial cuidado cuando comparas el valor cero con valores punto flotante ya que puedes obtener resultados inesperados. Por esta razón, comparamos contra un valor muy cercano a cero > 0.0000001 en la instrucción WHERE.
Por ejemplo, si ejecutamos php artisan serve y visitamos http://127.0.0.1:8000/, veremos todos los datos que se han agregado mediante los seeders. Pero, si pasamos el parámetro search con el valor estructura directorios de la siguiente manera http://127.0.0.1:8000/?search='estructura directorios', veremos como la consulta filtra estos resultados:
[
{
"id": 3,
"title": "Estructura de directorios de un proyecto en Laravel",
"content": "La estructura por defecto de aplicación de Laravel está pensada para proporcionar un buen punto de inicio tanto para grandes y pequeñas aplicaciones. Pero, eres libre de organizar tu aplicación como quieras. Laravel no impone casi ninguna restricción sobre donde una clase es ubicada - siempre y cuando Composer pueda cargar automáticamente la clase.",
"score": 1.8165581226348877
}
]
Las búsquedas de texto completo son insensitivas a mayúsculas, es decir, no importa si la «frase a buscar» está en mayúsculas o minúsculas, podemos ejecutar una consulta como la siguiente http://127.0.0.1:8000/?search='EsTructuRa DirecTorioS' y aún así obtener el mismo resultado anterior.
La frase estructura directorios son palabras claves que hemos elegido del documento storage/app/docs/structure.md, usado para llenar el campo content de la tabla chapters desde nuestro seeder ChapterSeeder.php. Pero eres libre de elegir la «frase» que prefieras para tu búsqueda.
¿Qué implica el hecho de que una frase sea relevante?
Hacer una consulta de texto completo es muy similar a las consultas que hacemos en un buscador, ya que también escribes palabras que ayuden a filtrar entre tantas posibilidades.
La frase completa estructura directorios tiene una relevancia de 1.8165581226348877, la cual es mucho mayor que buscar por palabras independientes, como estructura que aporta 1.2110387086868286 y directorios con 0.6055193543434143. Podemos decir entonces que entre estas dos palabras la primera es más relevante y por eso al unirlas en una frase filtramos mejor, ya que aumentamos el score de relevancia.
Una relevancia de cero significa que no hay similaridad, es como si estuvieras hablando de algo con una persona y de pronto le preguntaras por otra cosa que no tiene relación con el tema. Por ejemplo: http://127.0.0.1:8000/?search=caracoles.
Una relevancia es calculada en base al número de palabras en la fila o documento, el número de palabras únicas en el documento, el número total de palabras en la colección y el número de documentos que contienen una palabra particular.
¡Cuidado con las palabras que no aportan relevancia!
Observa que si consultamos estructura de directorios, agregando la palabra de a la frase no estaríamos haciendo ningún aporte, el score sería el mismo, y esto es porque la palabra que anexamos se excluye del análisis -como habíamos mencionado al principio.
Ahora, prueba con la siguiente consulta: http://127.0.0.1:8000/?search=Laravel y observa que devuelve un resultado vacío. Puedes constatar en la base de datos que la palabra Laravel aparece en todos los documentos de la colección.
Una palabra que aparezca muchas veces en el documento y la colección paradójicamente tampoco ofrece ningún aporte. Esto se debe al principio de que aquellas palabras de más de tres letras que sean muy frecuentemente son comunes, por tanto ayudan a filtrar muy poco y su relevancia es casi cero, al igual que las palabras excluidas.
El término documento es la porción del registro que incluye los campos del índice FULLTEXT, es decir, una entrada dentro del índice; mientras que colección se refiere al conjunto de todos los documentos en la tabla, lo que equivale al índice en sí.
Buscando por coincidencias exactas
Usualmente cuando hacemos comparaciones de igualdad entre cadenas de texto, si la frase varía por al menos un carácter, entonces ya no entra en el resultado final. Si deseas obtener este mismo comportamiento al realizar búsquedas de texto completo, debes colocar la «frase a buscar» entre comillas dobles (").
Si ejecutamos la siguiente consulta en el navegador http://127.0.0.1:8000/?search="estructura directorios", el resultado será vacío porque la única coincidencia posible se obtiene con la frase estructura de directorios, así: http://127.0.0.1:8000/?search="estructura de directorios".
El ordenamiento descendente de resultados
Cuando la función MATCH() se incluye en la cláusula WHERE, el resultado final se ordena por relevancia de forma descendente. Es decir, la consulta revisará todos los documentos pero solo va a extraer aquellos que cumplen con la condición del WHERE, y los valores con score más alto los mostrará de primero.
Agregamos la columna score dentro del SELECT de la consulta para que puedas visualizar esto de mejor forma.
Por ejemplo, si realizamos una consulta usando la frase composer: http://127.0.0.1:8000/?search=composer. Esta palabra tendrá 3 ocurrencias en el primer documento y su score de relevancia será mayor (0.2718571722507477) que en el resto de los documentos donde sólo aparece una vez (0.0906190574169159).
Nota que estos dos últimos documentos tienen el mismo score pues la ocurrencia de la frase es la misma.
Material relacionado
- Curso de Eloquent ORM
- ¡Composer 2.0 ya está disponible!
- Actualizando el instalador de paquetes de Laravel
- Cambios en Model Factories y Seeders a partir de Laravel 8
Regístrate hoy en Styde y obtén acceso a todo nuestro contenido.
Lección siguiente Búsquedas de texto completo en modo boolean con Laravel