cómo optimizar la base de datos mysql eficientemente

[gnzsoloyo]

Es un modo de hacerlo.
Pero el tema de la paginación o la cantidad de registros que almacenes en los arrays, no son tema de BBDD, sino de programación. A nosotros lo que nos interesa es cuán performántica es la consulta, no lo que haces con ella.
Si la consulta tarda 0,17 segundos en ejecutarse en servidor (muy probable dada la cantidad de registros), entonces tienes más tiempo de transacciones de red que de consulta y por tanto optimizar eso es irrelevante.
Pero si la consulta tarda 1, 2, 15, o 45 segundos en ejecutarse en el server, entonces tienes un problema de optimización de la consulta. Y eso es otro problema diferente, sobre el que sí podemos hablar.
Además de eso, para ver si es necesario o no lo que propones hay que ver otros detalles, por ejemplo:
- Si la tabla consultada es de elevado nivel de INSERT/UPDATE/DELETE, cargar en memoria es una mala idea, porque el usuario puede estar viendo datos irreales (lo que leyó, no lo que hay).
- Si las consultas traen más columnas de las necesarias, porque luego de seleccionar un registro pide detalles que no muestras al inicio, tu sistema es ineficiente y está transmitiendo enormes cantidades de "datos basura" (información no necesaria para la etapa del proceso), que bien puedes pedir después de modo más eficiente.
- Como todas las gestiones son asincrónicas, al no refrescar los datos, entonces ciertas cosas a realizar después pueden no poder hacerse.
- Bloquear las tablas para prevenir cambios, en tu contexto, generará bloqueos innecesarios y posibles casos de deadlocks.

En definitiva, yo no lo considero muy aconsejable, a menos que leyeses datos totalmente inmodificables.