Una pantalla lenta no demuestra que la base de datos sea el problema, ni que un índice sea la solución. La misma percepción puede originarse en código PHP, saturación de conexiones, una llamada HTTP externa, serialización de respuestas, bloqueos o una consulta que devuelve demasiados datos. Saber cómo investigar consultas lentas en PHP consiste en construir evidencia antes de modificar el esquema o añadir optimizaciones que podrían encarecer las escrituras.
Separar la latencia de la aplicación de la latencia de datos
Empiece por descomponer el tiempo total de una petición. Registre una ruta o comando identificable, el tiempo de inicio y fin, las consultas ejecutadas, sus duraciones y las dependencias externas. No basta con medir el tiempo medio: una API puede parecer saludable y, sin embargo, fallar para determinados filtros, clientes o páginas profundas.
Para cada incidente, conviene diferenciar:
- Tiempo en PHP: transformación de colecciones, bucles, serialización JSON, generación de documentos o uso excesivo de memoria.
- Tiempo de base de datos: duración de cada consulta, espera por bloqueos, apertura de conexión y número de filas transferidas.
- Tiempo de red y dependencias: cachés remotas, APIs de terceros, almacenamiento de archivos, colas o servicios de identidad.
- Tiempo de cola: peticiones que esperan trabajadores PHP, conexiones disponibles o recursos de la base de datos.
Use trazas o logs estructurados con un identificador de petición. Una consulta aislada de 20 ms puede convertirse en un problema de segundos si se ejecuta cientos de veces en la misma respuesta. A la inversa, una consulta de 500 ms puede no ser la causa principal si el proceso permanece esperando un servicio externo durante varios segundos.
Recoger evidencia antes de cambiar el código
Capture la consulta parametrizada y, por separado, parámetros representativos. Evite registrar secretos, datos personales completos o valores que no sean necesarios para reproducir el caso. Una búsqueda por un estado frecuente no se comporta igual que una búsqueda por un identificador único; evaluar sólo el caso cómodo conduce a decisiones erróneas.
La evidencia mínima debe incluir:
- Ruta, trabajo asíncrono o comando afectado, además de su frecuencia.
- Duración observada, percentiles altos y momento de aparición.
- SQL, parámetros tipificados y número de ejecuciones por petición.
- Filas devueltas y, cuando sea posible, filas leídas o examinadas.
- Tamaño aproximado de las tablas y distribución de los valores filtrados.
- Concurrencia, operaciones de escritura simultáneas y bloqueos relevantes.
Los registros lentos del motor ayudan a descubrir candidatos, pero no sustituyen una traza de aplicación: normalmente no indican qué endpoint construyó una consulta ni cuántas veces se repitió. En MySQL y PostgreSQL, combine esa información con métricas de conexión, CPU, I/O y tiempos de espera para no confundir un plan deficiente con una infraestructura temporalmente saturada.
Localizar patrones que multiplican el coste
Antes de analizar una sentencia compleja, busque patrones frecuentes. El N+1 aparece cuando un listado obtiene sus filas principales y después ejecuta una consulta adicional por cada relación. Aunque cada consulta individual sea rápida, el volumen de viajes a la base de datos, el trabajo de planificación y la contención crecen con el tamaño de la página.
También son sospechosos los filtros de baja selectividad, como estados muy comunes; funciones aplicadas sobre columnas filtradas; conversiones implícitas de tipo; búsquedas con comodín inicial; ordenaciones de conjuntos grandes y paginación mediante OFFSET elevado. Pedir SELECT * puede aumentar transferencia, memoria y trabajo de lectura, incluso cuando el plan ya utiliza un índice.
La corrección no siempre es una consulta única. Cargar relaciones de forma controlada puede resolver un N+1, pero una carga ansiosa sin límites puede crear una consulta o respuesta enorme. Defina qué relaciones necesita realmente la ruta, limite sus columnas y mida el efecto con el tamaño de página esperado.
Interpretar el plan de ejecución con datos reales
Ejecute EXPLAIN para conocer el plan propuesto y use la variante que incorpora ejecución real cuando sea segura y adecuada para el entorno. En PostgreSQL, EXPLAIN ANALYZE ejecuta la consulta; una sentencia de modificación no debe analizarse así sin entender su efecto. En MySQL, las modalidades disponibles dependen de la versión y configuración, pero el objetivo es el mismo: comparar estimaciones con trabajo real.
Un escaneo secuencial no es automáticamente malo. Si la consulta necesita una parte grande de una tabla pequeña o poco selectiva, recorrerla puede costar menos que saltar entre índice y tabla. En cambio, investigue cuando el plan muestra muchas más filas reales que estimadas, ordenaciones costosas, lecturas temporales, joins sobre conjuntos amplios o bucles internos ejecutados muchas veces.
Preguntas útiles al revisar un plan
- ¿Cuántas filas esperaba el optimizador y cuántas procesó realmente?
- ¿Qué nodo concentra más tiempo, lecturas o iteraciones?
- ¿El filtro se aplica pronto o después de combinar grandes conjuntos?
- ¿La ordenación ocurre sobre más filas de las que la respuesta necesita?
- ¿Las estadísticas reflejan la distribución actual de los datos?
Las discrepancias entre estimación y realidad pueden requerir actualizar estadísticas o revisar tipos y condiciones, no crear un índice de inmediato. El plan es una explicación de una ejecución bajo ciertos parámetros y carga; no una orden automática de cambio.
Elegir entre reescritura, paginación, acceso a datos e índice
Reduzca primero el trabajo inevitable. Seleccione sólo columnas necesarias, aplique límites razonables, elimine relaciones no utilizadas y evite transportar historiales completos a PHP para filtrarlos después. Si el caso de uso pide explorar un historial creciente, sustituya la paginación profunda por paginación por cursor o clave: por ejemplo, continuar desde una combinación estable de fecha e identificador en lugar de descartar miles de filas con OFFSET.
Revise joins y filtros para que comparen columnas compatibles y expresen la condición con claridad. A veces conviene consultar una relación en bloques; otras, una sola consulta bien delimitada es preferible. La decisión depende de cardinalidad, volumen de respuesta y frecuencia, no de una regla universal.
Un índice compuesto ayuda cuando coincide con el patrón de acceso. Su orden importa: normalmente las columnas de igualdad y selectivas deben facilitar el filtrado antes de las usadas para rango u ordenación, pero la consulta concreta y el motor determinan el resultado. Un índice puede además ayudar a evitar una ordenación si cubre un orden compatible, aunque no toda combinación de WHERE y ORDER BY lo permite.
Evite indexar columnas sólo porque aparecen en una condición. Los índices ocupan espacio, consumen memoria y añaden trabajo a INSERT, UPDATE y DELETE. Índices redundantes o de baja utilidad pueden empeorar un sistema con mucha escritura. Revise también si el índice permite obtener las columnas requeridas sin lecturas adicionales, pero no añada columnas de cobertura sin medir el coste.
Ejemplo hipotético: un historial de operaciones
Suponga un listado que muestra operaciones por cuenta, estado y fecha. Al crecer el historial, la página 200 se degrada. La primera hipótesis podría ser crear un índice sobre la fecha. Sin embargo, la traza revela una consulta principal seguida de una consulta por cada operación para obtener el usuario responsable: hay un N+1. El plan de la consulta principal además lee muchas filas para descartar las anteriores mediante OFFSET.
La secuencia razonable sería cargar los responsables de forma agrupada o mediante un join limitado a las columnas necesarias, reemplazar la paginación profunda por cursor basado en created_at e id, y volver a medir. Sólo entonces se evalúa un índice alineado con el filtro de cuenta, el orden estable y el cursor. El cambio de acceso puede reducir más trabajo que un índice aislado, y el índice final debe comprobarse también frente a la creación de nuevas operaciones.
Validar bajo carga y vigilar regresiones de escritura
Compare antes y después con parámetros, distribución de datos y concurrencia representativos. Mida duración, filas procesadas, lecturas, uso de CPU, memoria en PHP, tamaño de la respuesta y número de consultas por petición. Para un índice nuevo, mida también la latencia y capacidad de las operaciones de escritura afectadas.
Defina límites de aceptación explícitos: por ejemplo, una reducción verificable del percentil alto de la ruta sin aumentar de forma inaceptable el tiempo de creación o actualización. Pruebe casos vacíos, valores muy frecuentes, filtros raros, primeras y últimas páginas, y permisos que cambien el alcance de los datos.
Desplegar cambios de forma observable y reversible
Separe, cuando sea posible, el despliegue de código de la creación del índice. La creación de índices puede competir por recursos o adquirir bloqueos según el motor, la operación y el entorno. Planifique el momento, revise el método soportado por su base de datos y vigile la duración, errores y presión sobre I/O.
Introduzca la nueva consulta de forma gradual si la arquitectura lo permite y mantenga una reversión clara: restaurar la consulta anterior, desactivar una ruta de acceso alternativa o eliminar un índice que demuestre ser perjudicial. Una reversión no reemplaza las copias de seguridad ni la revisión de migraciones, pero reduce el tiempo de exposición ante un comportamiento inesperado.
Checklist para una mejora repetible
- Identifique la ruta, el síntoma y los parámetros que lo reproducen.
- Separe tiempo de PHP, base de datos, red y dependencias externas.
- Cuantifique repeticiones, filas devueltas y filas procesadas.
- Busque N+1, paginación profunda, filtros poco selectivos y ordenaciones.
- Revise el plan y contraste sus estimaciones con la ejecución real.
- Pruebe reducción de datos, reescritura y cambio de paginación antes de indexar.
- Diseñe el índice según el patrón completo de filtro, ordenación y escritura.
- Valide lecturas y escrituras con carga representativa, observabilidad y reversión.
