Curso de MariaDB / Mejorar filtros, ordenación y paginación sin cambiar resultados

Mejorar una consulta exige conservar las filas previstas y adaptar sus filtros, ordenación y paginación a vías de acceso adecuadas. Defina límites temporales y desempates únicos, compare OFFSET con la navegación por claves y compruebe cualquier mejora de rendimiento con planes reales y datos representativos.

Comprobar la equivalencia antes de comparar el coste

Una consulta más rápida que pierde fechas límite o filas empatadas no es una mejora equivalente. Compare primero las claves primarias devueltas y su secuencia; después revise planes, trabajo y latencia. Eliminar un requisito para reducir el coste cambia el significado de la consulta. La guía de ORDER BY y LIMIT explica los límites de ordenación y paginación, y la comparación de planes con EXPLAIN permite revisar el acceso antes y después.

Aspecto Decisión Ejemplo
Filtro temporal Inicio incluido y siguiente límite excluido Un día completo
Ordenación Desempate determinista ordered_at, order_id
Paginación Navegación y coherencia requeridas OFFSET o últimos valores de orden
Búsqueda de texto Patrón que realmente se necesita Prefijo y contenido no son lo mismo

Preparar las tablas y el índice temporal

Conéctese como course_user al servidor de prácticas y seleccione shopcourse. La guía del cliente y los archivos SQL UTF-8 detalla cómo guardar el código y ejecutarlo dentro del cliente con SOURCE C:/mariadb-lab/lesson.sql;, sustituyendo la ruta por la real. No introduzca el SQL directamente en la consola del sistema operativo.

Compruebe SELECT VERSION(), DATABASE(); y SHOW TABLES; antes de crear c41_customers, c41_products, c41_orders y c41_items. Los nombres son exclusivos de estas prácticas. Si existen, inspeccione sus definiciones y registros antes de decidir si son prescindibles; no sustituya tablas de una aplicación.

USE shopcourse;
CREATE TABLE c41_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c41_order_customer FOREIGN KEY (customer_id)
  REFERENCES c41_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c41_item_order FOREIGN KEY (order_id)
  REFERENCES c41_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c41_item_product FOREIGN KEY (product_id)
  REFERENCES c41_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c41_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c41_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c41_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c41_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Se mantienen los nombres y las ciudades originales en coreano. Los productos 1–4 son un cuaderno, un bolígrafo, una taza y una lámpara. Los clientes 1 y 3 viven en Seúl; el 2 en Busan y el 4 en Daejeon. Mantenga esos literales UTF-8 para reproducir los resultados.

CREATE INDEX idx_c41_time ON c41_orders(ordered_at,order_id);

Los cuatro pedidos se pueden comparar por fecha y hora e identificador. Para una tabla tan pequeña, el optimizador puede elegir un recorrido completo razonablemente. El juego de datos permite verificar las filas y los límites; estudiar efectos de rendimiento mayores requiere datos representativos.

Comparar DATE con un intervalo explícito

SELECT order_id FROM c41_orders WHERE DATE(ordered_at)='2026-01-10'
ORDER BY order_id;
SELECT order_id FROM c41_orders
WHERE ordered_at>='2026-01-10' AND ordered_at<'2026-01-11' ORDER BY order_id;

Las dos consultas devuelven el pedido 101. El intervalo expresa el día completo: incluye el comienzo del 10 de enero y excluye el comienzo del 11. Esta forma sigue expresando el límite del día cuando la columna admite fracciones de segundo; evita depender de un final escrito como 23:59:59, que dejaría fuera fracciones posteriores de ese segundo.

Desde MariaDB 11.1, ciertas condiciones DATE y YEAR sobre columnas temporales indexadas pueden transformarse en condiciones que permiten acceso por índice. No afirme que aplicar una función a una columna impide siempre aprovecharlo. La documentación oficial de la optimización DATE y YEAR especifica las formas y tipos admitidos.

Un intervalo explícito puede hacer visible la intención entre versiones, pero no demuestra por sí solo una velocidad superior. Compare los accesos con EXPLAIN. Dar formato de texto a una fecha para buscarla puede mezclar presentación y filtrado; revise también los tipos comparados.

Usar fecha e identificador para avanzar a la página siguiente

SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
 OR(ordered_at='2026-02-05 12:00:00' AND order_id<103)
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;

La primera página descendente contiene 104 y 103. El último registro de esa página es el 103, con fecha 2026-02-05 12:00:00. El segundo SELECT utiliza ambos valores como cursor y devuelve 102 y 101: solicita fechas anteriores o, si la fecha coincide, un ID menor. Revise también el sentido de las comparaciones si cambia la dirección de ordenación.

Esta navegación por claves, también llamada keyset, puede evitar offsets profundos, pero no ofrece por sí sola el mismo salto directo a cualquier número de página. Los cambios concurrentes pueden seguir alterando la lista entre peticiones. Decida si necesita una instantánea fija y qué reglas debe seguir la aplicación; una clave de cursor no congela el conjunto completo.

El siguiente bloque prueba un empate justo en el límite de página mediante un cambio reversible:

START TRANSACTION;
UPDATE c41_orders SET ordered_at='2026-02-05 12:00:00' WHERE order_id=104;
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
   OR (ordered_at='2026-02-05 12:00:00' AND order_id<104)
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
ROLLBACK;

La primera consulta devuelve 104 y la siguiente devuelve 103 con la misma fecha y hora. Un límite basado únicamente en una fecha estrictamente anterior podría saltarse el 103. Después de ROLLBACK, compruebe que el pedido 104 recupera 2026-02-10 13:00:00. Ejecute el bloque completo en la misma conexión sobre las tablas InnoDB indicadas.

Revisar columnas, selectividad y patrón de búsqueda

SELECT * puede transferir textos grandes innecesarios y aumentar memoria o lecturas de filas. Reducir las columnas solicitadas no equivale a reducir los registros que cumplen el filtro. Revise en el plan si los valores necesarios pueden obtenerse mediante un índice de cobertura.

En un índice B-tree ordinario, buscar un prefijo con LIKE y buscar contenido con un comodín inicial presentan posibilidades de acceso distintas. Cambiar una búsqueda de contenido por una de prefijo puede parecer más rápido mientras cambia los resultados. Defina el patrón y la intercalación, es decir, las reglas de comparación del texto, y valore una estrategia de búsqueda que responda a ese requisito.

La guía oficial de diseño de índices para un SELECT ayuda a relacionar filtros y ordenación con claves candidatas; confirme las decisiones en la consulta y versión reales.

Registrar las condiciones de la comparación

Conserve ambas consultas, las claves devueltas, la versión del servidor, los índices y el número y distribución de filas. Distinga efectos de caché de mejoras estructurales y no imponga un índice únicamente para obtener el plan esperado. Revise también los requisitos de ordenación y paginación de las consultas que compartan la misma vía de acceso.

Antes de repetir la preparación, revise la base seleccionada, las definiciones y los registros; respalde lo que deba conservar. Si las cuatro tablas siguientes son solo objetos prescindibles de prácticas, elimínelas en este orden, primero las hijas y después las referenciadas. DROP TABLE elimina estructuras y datos y no se revierte con un ROLLBACK ordinario; mantenga la limpieza separada de la prueba de empate transaccional.

DROP TABLE shopcourse.c41_items;
DROP TABLE shopcourse.c41_orders;
DROP TABLE shopcourse.c41_products;
DROP TABLE shopcourse.c41_customers;