Capacidades de Búsqueda de Texto Completo en MySQL

Introducción

En este laboratorio, explorará las capacidades de búsqueda de texto completo (full-text search) de MySQL. Comenzará creando una base de datos y una tabla llamada articles con columnas para id, title y content, y luego la poblará con datos de ejemplo.

A continuación, agregará un índice de texto completo (full-text index) llamado article_index a la tabla articles, específicamente en las columnas title y content, utilizando la sentencia ALTER TABLE. Finalmente, verificará la creación del índice utilizando el comando SHOW INDEXES, confirmando que el Index_type es FULLTEXT. Esta configuración lo preparará para búsquedas de texto eficientes dentro de su base de datos MySQL.

A lo largo de este laboratorio, trabajará dentro del shell de MySQL. Solo necesita ingresar al shell de MySQL al principio y salir al final. Todos los comandos SQL en los siguientes pasos deben ejecutarse dentro de la misma sesión de MySQL.

Crear Base de Datos y Tabla

En este paso, creará una base de datos MySQL y una tabla para almacenar artículos. Esta tabla se utilizará para demostrar las capacidades de búsqueda de texto completo (full-text search).

Primero, abra su terminal y conéctese al servidor MySQL como usuario root. Puede hacerlo utilizando el siguiente comando:

sudo mysql -u root

Este comando se conecta al servidor MySQL utilizando el usuario root. Dado que está utilizando sudo, no se le pedirá una contraseña.

Una vez conectado al shell de MySQL, verá el prompt mysql>. Permanezca en el shell de MySQL para todos los pasos subsiguientes hasta el final del laboratorio.

Ahora, creemos una base de datos llamada search_db. Esta base de datos contendrá nuestra tabla articles. Ejecute el siguiente comando SQL:

CREATE DATABASE IF NOT EXISTS search_db;

La cláusula IF NOT EXISTS asegura que la base de datos solo se cree si no existe previamente.

A continuación, cambie a la base de datos recién creada utilizando el comando USE:

USE search_db;

Debería ver un mensaje indicando que la base de datos ha cambiado.

Ahora, cree la tabla articles. Esta tabla tendrá tres columnas: id, title y content.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Analicemos este comando:

• CREATE TABLE articles: Esta sentencia crea una nueva tabla llamada articles.
• id INT AUTO_INCREMENT PRIMARY KEY: Esto define una columna de tipo entero llamada id que se incrementa automáticamente para cada nueva fila y sirve como clave primaria (primary key) de la tabla.
• title VARCHAR(255) NOT NULL: Esto define una columna de tipo cadena (string) llamada title con una longitud máxima de 255 caracteres. NOT NULL significa que esta columna no puede estar vacía.
• content TEXT: Esto define una columna de tipo texto llamada content que puede almacenar cadenas más largas.

Después de ejecutar este comando, la tabla articles se creará dentro de la base de datos search_db.

Puede verificar la creación de la tabla listando las tablas en la base de datos actual:

SHOW TABLES;

Debería ver articles listada en la salida.

+-----------------------+
| Tables_in_search_db   |
+-----------------------+
| articles              |
+-----------------------+
1 row in set (0.00 sec)

Ahora que ha creado la base de datos y la tabla, está listo para poblarla con algunos datos de ejemplo en el siguiente paso.

Insertar Datos de Muestra y Agregar Índice de Texto Completo

En este paso, insertará datos de ejemplo en la tabla articles y luego añadirá un índice de texto completo (full-text index) para permitir búsquedas de texto eficientes.

Asegúrese de que todavía está en el shell de MySQL y utilizando la base de datos search_db. Si no es así, seleccione la base de datos:

USE search_db;

Ahora, insertemos algunos datos de ejemplo en la tabla articles. Añadiremos tres filas con diferentes títulos y contenidos:

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');

Esta sentencia INSERT añade múltiples filas a la tabla articles. Cada conjunto de paréntesis después de VALUES representa una nueva fila, con los valores correspondientes a las columnas title y content.

Puede verificar que los datos se han insertado seleccionando todas las filas de la tabla:

SELECT * FROM articles;

Debería ver las tres filas insertadas en la salida:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Ahora que la tabla tiene datos, añadamos un índice de texto completo. Un índice de texto completo permite a MySQL realizar búsquedas rápidas y relevantes en datos de texto. Añadiremos un índice de texto completo tanto en las columnas title como content.

Utilice la sentencia ALTER TABLE para añadir el índice:

ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);

Analicemos este comando:

• ALTER TABLE articles: Esto indica que estamos modificando la tabla articles.
• ADD FULLTEXT INDEX article_index: Esto añade un nuevo índice de tipo FULLTEXT y lo nombra article_index.
• (title, content): Esto especifica las columnas que se incluirán en el índice de texto completo.

Después de ejecutar este comando, MySQL creará el índice de texto completo en las columnas especificadas.

Para verificar que el índice de texto completo se ha creado correctamente, puede utilizar el comando SHOW INDEXES:

SHOW INDEXES FROM articles;

La salida mostrará todos los índices de la tabla articles. Debería ver una entrada para article_index con FULLTEXT en la columna Index_type.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| articles |          1 | article_index  |            1 | title       | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
| articles |          1 | article_index  |            2 | content     | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Ahora ha insertado correctamente los datos y ha añadido un índice de texto completo a su tabla. En el siguiente paso, aprenderá a utilizar este índice para realizar búsquedas de texto completo.

Realizar Búsqueda Básica de Texto Completo

En este paso, realizará búsquedas básicas de texto completo en la tabla articles utilizando la cláusula MATCH AGAINST. Esta es la forma fundamental de utilizar el índice de texto completo que creó.

Asegúrese de que todavía está en el shell de MySQL y utilizando la base de datos search_db. Si no es así, seleccione la base de datos:

USE search_db;

La cláusula MATCH AGAINST se utiliza dentro de la cláusula WHERE de una sentencia SELECT. La sintaxis básica es:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term');

Aquí, column_list es una lista separada por comas de las columnas incluidas en el índice de texto completo (en nuestro caso, title y content), y 'search_term' es la palabra o frase que desea buscar.

Busquemos artículos que contengan la palabra "MySQL":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');

Esta consulta selecciona las columnas id, title y content de la tabla articles donde las columnas title o content coinciden con el término "MySQL".

Debería ver la siguiente salida:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Se devuelven los tres artículos porque todos contienen la palabra "MySQL".

La búsqueda de texto completo también proporciona una puntuación de relevancia (relevance score), que indica qué tan bien coincide cada fila con el término de búsqueda. Puede incluir esta puntuación en su sentencia SELECT:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;

Esta consulta añade una columna llamada relevance que muestra la puntuación de cada fila.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

Puede ordenar los resultados por relevancia para ver primero las mejores coincidencias:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;

Esta consulta ordena los resultados basándose en la puntuación de relevance en orden descendente.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

En este paso, aprendió a realizar búsquedas básicas de texto completo utilizando MATCH AGAINST y cómo recuperar y ordenar resultados por relevancia. En el siguiente paso, explorará opciones de búsqueda más avanzadas utilizando el modo booleano (boolean mode).

Usar Modo Booleano para Búsqueda Avanzada

En este paso, utilizará el modo booleano de la cláusula MATCH AGAINST para realizar búsquedas de texto completo más precisas y flexibles. El modo booleano le permite usar operadores para controlar qué palabras deben o no deben estar presentes en los resultados.

Asegúrese de que todavía está en el shell de MySQL y utilizando la base de datos search_db. Si no es así, seleccione la base de datos:

USE search_db;

Para usar el modo booleano, añade la cláusula IN BOOLEAN MODE a la sentencia MATCH AGAINST. La sintaxis es:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term' IN BOOLEAN MODE);

Aquí hay algunos operadores comunes utilizados en el modo booleano:

• +: Requiere que la palabra esté presente en el resultado.
• -: Excluye filas que contienen la palabra.
• "": Busca una frase exacta.
• *: Operador comodín (al final de una palabra).

Busquemos artículos que deban contener la palabra "MySQL" y no deban contener la palabra "optimizing".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);

Esta consulta utiliza el operador + para requerir "MySQL" y el operador - para excluir "optimizing".

Debería ver la siguiente salida:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

El artículo sobre "Optimizing MySQL Queries" se excluye porque contiene "optimizing".

Ahora, busquemos la frase exacta "full-text search":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);

Usar comillas dobles busca las palabras como una frase contigua.

La salida será:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Solo se devuelve el primer artículo porque contiene la frase exacta "full-text search".

Intentemos usar el operador comodín *. Supongamos que desea encontrar artículos que contengan palabras que comiencen con "index".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('index*' IN BOOLEAN MODE);

El término index* coincidirá con palabras como "index" e "indexing".

La salida será:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

En este paso, aprendió a usar el modo booleano con la cláusula MATCH AGAINST y varios operadores para realizar búsquedas de texto completo más controladas y específicas. En el siguiente paso, practicará la combinación de estas técnicas.

Combinar Técnicas de Búsqueda

En este paso, combinará la búsqueda básica MATCH AGAINST con operadores de modo booleano para realizar consultas de texto completo más complejas y refinadas.

Asegúrese de que todavía está en el shell de MySQL y utilizando la base de datos search_db. Si no es así, seleccione la base de datos:

USE search_db;

Intentemos encontrar artículos que contengan la palabra "MySQL" y, ya sea la palabra "indexing" o "optimizing". Podemos usar paréntesis para agrupar los términos opcionales en el modo booleano.

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);

Esta consulta requiere "MySQL" (+MySQL) y requiere al menos uno de los términos dentro de los paréntesis (+(indexing optimizing)).

La salida será:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

El primer artículo se excluye porque contiene "MySQL" pero ni "indexing" ni "optimizing".

También puede combinar el modo booleano con la puntuación de relevancia. Busquemos artículos que contengan "MySQL" y prioricemos aquellos que también mencionen "indexing" usando el operador >.

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;

Esta consulta requiere "MySQL" y aumenta la puntuación de relevancia para los artículos que contienen "indexing". Luego, los resultados se ordenan por la relevancia calculada.

La salida será:

+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title                    | content                                                             | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |  1.6931  |
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |  0.3068  |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |  0.3068  |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

Observe que el artículo "Indexing in MySQL" se clasifica más alto debido al operador >indexing.

Al combinar la búsqueda básica con operadores booleanos y puntuación de relevancia, puede crear consultas de texto completo potentes y flexibles adaptadas a sus necesidades específicas.

Esto concluye el laboratorio sobre las capacidades de búsqueda de texto completo de MySQL. Ahora puede salir del shell de MySQL escribiendo:

exit

Ha aprendido a configurar una tabla con un índice de texto completo, a realizar búsquedas básicas y a utilizar el modo booleano para consultas más avanzadas.

Resumen

En este laboratorio, ha aprendido sobre las capacidades de búsqueda de texto completo de MySQL. Comenzó creando una base de datos llamada search_db y una tabla articles para almacenar datos textuales. Luego, pobló la tabla con artículos de ejemplo.

El paso clave fue agregar un índice de texto completo llamado article_index a las columnas title y content de la tabla articles utilizando la sentencia ALTER TABLE. Este índice es esencial para una búsqueda de texto completo eficiente. Verificó la creación del índice usando SHOW INDEXES.

Finalmente, exploró cómo realizar búsquedas de texto completo utilizando la cláusula MATCH AGAINST. Aprendió a realizar búsquedas básicas, recuperar puntuaciones de relevancia y ordenar los resultados por relevancia. También profundizó en el modo booleano, utilizando operadores como +, -, "" y * para crear consultas de búsqueda más precisas y complejas. Al combinar estas técnicas, puede buscar y recuperar de manera efectiva información relevante de los datos de texto almacenados en su base de datos MySQL.