Capacidades de búsqueda de texto completo en MySQL

Introducción

En este laboratorio, explorarás las capacidades de búsqueda de texto completo de MySQL. Comenzarás creando una base de datos y una tabla llamada articles con columnas para id, title y content, y luego la llenarás con datos de muestra.

A continuación, agregarás un índice de texto completo llamado article_index a la tabla articles, específicamente en las columnas title y content, utilizando la declaración ALTER TABLE. Finalmente, verificarás la creación del índice utilizando el comando SHOW INDEXES, confirmando que el Index_type es FULLTEXT. Esta configuración te prepara para realizar búsquedas de texto eficientes en tu base de datos MySQL.

Skills Graph

Agregar un índice de texto completo a una tabla

En este paso, agregaremos un índice de texto completo a una tabla en una base de datos MySQL. Los índices de texto completo se utilizan para realizar búsquedas de texto eficientes en datos basados en caracteres.

Antes de comenzar, creemos una tabla simple llamada articles con la que trabajar. Abre tu terminal y conéctate al servidor MySQL utilizando el siguiente comando:

mysql -u root -p

Se te pedirá la contraseña de root. Ingresa la contraseña para conectarte.

Ahora, creemos una base de datos llamada search_db y cambiemos a ella:

CREATE DATABASE IF NOT EXISTS search_db;
USE search_db;

A continuación, crea la tabla articles con columnas para id, title y content:

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

Ahora, insertemos algunos datos de muestra en la tabla articles:

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.');

Para agregar un índice de texto completo a la tabla articles, utilizamos la declaración ALTER TABLE. Crearemos un índice de texto completo en las columnas title y content. Ejecuta el siguiente comando SQL:

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

Este comando crea un índice de texto completo llamado article_index que incluye las columnas title y content. MySQL ahora podrá buscar de manera eficiente estas columnas utilizando las capacidades de búsqueda de texto completo.

Puedes verificar que el índice se haya creado utilizando el comando SHOW INDEXES:

SHOW INDEXES FROM articles;

La salida debe incluir una fila para el índice de texto completo article_index. La columna Index_type de este índice mostrará FULLTEXT.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 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 has agregado con éxito un índice de texto completo a tu tabla. En los siguientes pasos, aprenderás cómo utilizar este índice para realizar búsquedas de texto completo.

Búsqueda con MATCH AGAINST

En este paso, utilizaremos la cláusula MATCH AGAINST para realizar búsquedas de texto completo en la tabla articles que creamos en el paso anterior.

Primero, asegúrate de estar conectado al servidor MySQL y utilizando la base de datos search_db. Si cerraste la conexión, vuelve a conectarte utilizando:

mysql -u root -p

Ingresa la contraseña de root cuando se te solicite. Luego, selecciona la base de datos:

USE search_db;

La cláusula MATCH AGAINST se utiliza en la cláusula WHERE de una declaración SELECT para realizar la búsqueda de texto completo. La sintaxis básica es:

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

Aquí, column1, column2, etc., son las columnas que forman parte del índice de texto completo, y 'search_term' es el texto que deseas buscar.

Realicemos una búsqueda simple de artículos que contengan la palabra "MySQL":

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

Esta consulta devolverá todas las filas de la tabla articles donde la columna title o la columna content contengan la palabra "MySQL".

Deberías 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)

Observa que se devuelven los tres artículos porque todos contienen la palabra "MySQL" en el título o en el contenido.

La cláusula MATCH AGAINST también se puede utilizar para clasificar los resultados según su relevancia. MySQL asigna una puntuación de relevancia a cada fila en función de cuán bien coincide con el término de búsqueda. Puedes recuperar esta puntuación utilizando la cláusula MATCH AGAINST en la lista SELECT:

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

Esta consulta devuelve el id, el title, el content y una puntuación de relevance para cada artículo. Cuanto mayor sea la puntuación de relevancia, mejor será la coincidencia.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 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)

Luego, puedes ordenar los resultados por relevancia para mostrar las mejores coincidencias primero:

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

Esta consulta devolverá los mismos resultados que antes, pero ordenados por 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, aprendiste cómo utilizar la cláusula MATCH AGAINST para realizar búsquedas básicas de texto completo y cómo clasificar los resultados por relevancia. En el siguiente paso, aprenderás cómo utilizar el modo booleano para capacidades de búsqueda más avanzadas.

Utilizar el modo booleano para búsquedas avanzadas

En este paso, exploraremos el modo booleano de la cláusula MATCH AGAINST, que permite realizar búsquedas de texto completo más precisas y flexibles. El modo booleano proporciona operadores para especificar palabras obligatorias, prohibidas y opcionales en la consulta de búsqueda.

Primero, asegúrate de estar conectado al servidor MySQL y utilizando la base de datos search_db. Si cerraste la conexión, vuelve a conectarte utilizando:

mysql -u root -p

Ingresa la contraseña de root cuando se te solicite. Luego, selecciona la base de datos:

USE search_db;

Para utilizar el modo booleano, se agrega la cláusula IN BOOLEAN MODE a la declaración MATCH AGAINST. La sintaxis es:

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

A continuación, se presentan algunos de los operadores disponibles en el modo booleano:

• +: La palabra debe estar presente en cada fila que se devuelva.
• -: La palabra no debe estar presente en ninguna fila que se devuelva.
• >: La palabra contribuye a la relevancia de la fila.
• <: La palabra reduce la relevancia de la fila.
• ~: La palabra contribuye negativamente a la relevancia de la fila.
• *: El operador comodín.
• "": Define una frase.

Comencemos con un ejemplo sencillo. Supongamos que queremos encontrar artículos que deben contener la palabra "MySQL" y no deben contener la palabra "optimizing". Podemos utilizar la siguiente consulta:

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

Esta consulta devolverá las filas que contengan "MySQL" pero no contengan "optimizing".

Deberías 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)

Observa que el artículo con el título "Optimizing MySQL Queries" no se devuelve porque contiene la palabra "optimizing".

Ahora, probemos una consulta que requiera "MySQL" y prefiera "indexing":

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

En el modo booleano, sin ningún operador, las palabras se tratan como opcionales. Esta consulta requiere "MySQL" e incluye opcionalmente "indexing".

La salida será:

+----+--------------------------+---------------------------------------------------------------------+
| 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 "MySQL". La presencia de "indexing" es opcional.

Utilicemos el operador > para aumentar la relevancia de las filas que contengan "indexing":

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

Esta consulta sigue requiriendo "MySQL", pero ahora las filas que contengan "indexing" se clasificarán más alto (aunque en este ejemplo no estamos ordenando por relevancia). La salida sigue siendo la misma, pero las puntuaciones de relevancia internas se ven afectadas.

Finalmente, busquemos una frase utilizando comillas dobles:

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

Esta consulta buscará la frase exacta "full-text search".

La salida será:

+----+------------------------+---------------------------------------------------------------------+
| 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)

En este paso, aprendiste cómo utilizar el modo booleano con la cláusula MATCH AGAINST para realizar búsquedas de texto completo más avanzadas y precisas utilizando operadores como +, -, >, < y "". En el siguiente paso, probarás y perfeccionarás tus consultas de texto completo.

Probar los resultados de las consultas de texto completo

En este paso, probaremos y refinaremos nuestras consultas de texto completo para asegurarnos de que devuelvan los resultados deseados. Experimentaremos con diferentes términos de búsqueda y operadores booleanos para lograr la mayor precisión posible en las búsquedas.

Primero, asegúrate de estar conectado al servidor MySQL y utilizando la base de datos search_db. Si cerraste la conexión, vuelve a conectarte utilizando:

mysql -u root -p

Ingresa la contraseña de root cuando se te solicite. Luego, selecciona la base de datos:

USE search_db;

Comencemos revisando los datos en nuestra tabla articles:

SELECT * FROM articles;

Esto mostrará el contenido actual de la tabla:

+----+--------------------------+---------------------------------------------------------------------+
| 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, probemos una consulta booleana más compleja. Supongamos que queremos encontrar artículos que contengan la palabra "MySQL" y ya sea "indexing" o "optimizing". Podemos lograr esto utilizando paréntesis para agrupar los términos opcionales:

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

Esta consulta requiere "MySQL" y luego requiere que esté presente ya sea "indexing" o "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 artículo 1 se excluye porque solo contiene "MySQL" y no "indexing" ni "optimizing".

Probemos otro escenario. Supongamos que queremos encontrar artículos que hablen de "MySQL" pero específicamente no de "queries".

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

Esta consulta requiere "MySQL" y excluye los artículos que contengan "queries".

La salida será:

+----+------------------------+---------------------------------------------------------------------+
| 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 3, que trata sobre "MySQL Queries", se excluye.

Ahora, experimentemos con la clasificación de los resultados. Podemos utilizar los operadores > y < para influir en la puntuación de relevancia. Digamos que queremos encontrar artículos sobre "MySQL", pero queremos priorizar los artículos que también mencionen "indexing":

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 relevancia de los artículos que contengan "indexing". Los resultados se ordenan luego por relevancia en orden descendente.

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)

Observa que el artículo "Indexing in MySQL" ahora aparece primero porque tiene una puntuación de relevancia más alta debido al término >indexing.

Al experimentar con diferentes términos de búsqueda y operadores booleanos, puedes ajustar tus consultas de texto completo para obtener los resultados deseados. Recuerda siempre probar tus consultas exhaustivamente para asegurarte de que funcionen como se espera.

Con esto se concluye el laboratorio sobre búsquedas de texto completo en MySQL. Has aprendido cómo agregar un índice de texto completo, realizar búsquedas básicas con MATCH AGAINST, utilizar el modo booleano para búsquedas avanzadas y probar los resultados de tus consultas.

Resumen

En este laboratorio, comenzamos creando una base de datos llamada search_db y una tabla articles con las columnas id, title y content. Luego, se insertaron datos de muestra en la tabla para llenarla con artículos.

El núcleo de este paso consistió en agregar un índice de texto completo llamado article_index a la tabla articles, específicamente en las columnas title y content, utilizando la declaración ALTER TABLE. Esto permite realizar búsquedas de texto de manera eficiente. Finalmente, verificamos la creación del índice utilizando el comando SHOW INDEXES, confirmando que el Index_type es FULLTEXT.