Consultas paginadas en MySql.

El consumo de información accediendo a bases de datos es clave para cualquier tipo de aplicación, por lo que es necesario que el consumo de la información se lleve a cabo en el menor tiempo posible y utilizando el menor número de recursos del servidor; esto con el fin de mejorar el tiempo de respuesta de las aplicaciones.

Es un hecho que muchas veces los desarrolles se enfrentan al reto de mostrar una consulta de información en un grid, el cual en todos los casos debe de tener elementos gráficos de paginación para que el usuario pueda navegar entre el conjunto de datos. En el escenario que se plantea, generalmente los desarrolladores hacen uso de malas prácticas, y a que voy con esto, a que por facilidad obtienen todo el conjunto de información de la base de datos y en capas superiores de la arquitectura de su aplicación empiezan a paginarla para cargarla en el grid para cada una de las paginas, lo que realmente es un consumo incorrecto de la información, ya que la manera más correcta de llevar a cabo el proceso es solicitar solamente aquellos datos que necesitas mostrar al usuario para la página que desea visualizar del grid, logrando así un óptimo tiempo de respuesta en la aplicación tanto en la visualización y paginación de la información en el grid.

A continuación te presento un ejemplo de cómo crear consultas paginadas desde base de datos, el cual puedes tomar como referencia para crear las consultas de datos de tus proyectos.

Creación de Base de Datos de Prueba:

create database BD_Pruebas;

use BD_Pruebas;

create table Persona
(
personaId int not null auto_increment primary key,
nombre varchar(20) not null,
apellidoPaterno varchar(20) not null,
apellidoMaterno varchar(20) not null,
genero varchar(9) not null,
fechaNacimiento datetime not null,
fechaCreacion datetime not null,
activo tinyint(1) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Creación de Insumos:

INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Armando','Rosales','Maza','Masculino','1988-10-12',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Luis','Perez','Hernandez','Masculino','1980-10-01',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Andrea','Sanchez','Gonzalez','Femenino','1988-01-20',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Monica','Lopez','Jimenez','Femenino','1970-09-15',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Hugo','Morales','Quiroz','Masculino','1985-04-18',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('David','Gonzalez','Rodriguez','Masculino','2000-01-29',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Karime','Lozano','Herrera','Femenino','1995-11-15',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Mario','Fernandez','Gutierrez','Masculino','2001-04-17',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Liliana','Duran','Roano','Femenino','1984-09-25',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Francisco','Zuñiga','Zapata','Masculino','1990-01-06',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Diego','Martinez','Roman','Masculino','1986-02-14',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jimena','Ortiz','Morales','Femenino','1993-12-24',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Tania','Ordaz','Montiel','Femenino','1987-07-05',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Ana','Arriaga','Linares','Femenino','1996-10-16',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Alejandra','Tamez','Vazquez','Femenino','2002-05-28',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Francisco','Mota','Marin','Masculino','1978-06-29',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Martin','Lutero','Lara','Masculino','1980-09-25',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Kendra','Muñiz','Mora','Femenino','1976-01-20',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Cristian','Beltran','Solis','Masculino','1987-11-15',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Natalia','Montes','Herrera','Femenino','1987-03-16',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Kenia','Martinez','Rodriguez','Femenino','1994-04-27',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Ricardo','Maldonado','Soto','Masculino','1998-11-02',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Susana','Perez','Lopez','Femenino','1992-05-20',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Ingrid','Solano','Jimenez','Femenino','1998-09-28',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Gerardo','Montiel','Oronzor','Masculino','1970-01-09',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Gabriel','Robles','Martinez','Masculino','1978-05-24',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Fany','Roman','Nuñez','Femenino','1990-08-09',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jamil','Morales','Santos','Masculino','1996-12-23',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Tamara','Sanchez','Ramirez','Femenino','1970-01-02',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Bernardo','Lopez','Gonzalez','Masculino','1995-10-10',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jaime','Jimenez','Sandria','Masculino','1980-12-10',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Mabel','Fernandez','Martinez','Femenino','1997-05-12',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jacobo','Montes','Ortiz','Masculino','1978-06-22',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Maria','Duran','Zamora','Femenino','1999-09-07',now(),1);

Procedimiento almacenado para encapsular la función de paginación y su respectiva invocación.

delimiter //
create procedure ObtenDatosPaginados(in numeroPagina int, in tamanioPagina int)
begin
	if (numeroPagina < 1 or tamanioPagina < 1) then
		select *, 0 as 'TotalRegistros', 0 as 'TotalPaginas' 
        from Persona
		limit 0,0;
	else
		begin
			declare _totalRegistros int default (select count(*) from Persona);                        
            declare _totalPaginas int default 0;
            declare _offset int default 0;            
                        		
            if ((_totalRegistros % tamanioPagina) = 0) then
				set _totalPaginas = _totalRegistros / tamanioPagina;                
			else
				set _totalPaginas= floor((_totalRegistros / tamanioPagina) + 1);                
            end if;
			
            set _offset = tamanioPagina * (numeroPagina - 1);                      
						
			select *, _totalRegistros as 'TotalRegistros', _totalPaginas as 'TotalPaginas'
			from Persona			
            order by nombre
            limit _offset, tamanioPagina;
        end;
	end if;
end
//
delimiter ;

call ObtenDatosPaginados(1,10);

PaginacionMySql

Agregar comentario

Loading