Optimización MySQL PrestaShop | Consultas para alto tráfico

La paradoja del crecimiento: cuando el éxito ralentiza tu tienda

Cuando tu tienda PrestaShop empieza a recibir cientos o miles de visitas simultáneas, el primer síntoma suele ser el mismo: la página se vuelve lenta. El culpable más frecuente no es el hosting ni el tema, sino las consultas a la base de datos. MySQL en PrestaShop está diseñado para funcionar en entornos de carga media, pero bajo alto tráfico ecommerce, las consultas SQL no optimizadas se convierten en un cuello de botella crítico.

En este artículo vas a descubrir técnicas concretas para optimización consultas SQL en PrestaShop, desde la configuración del motor de base de datos hasta la reescritura de queries problemáticas. No hablamos de teoría abstracta: cada bloque de código y cada recomendación están probados en entornos de producción con alto tráfico.

Diagnóstico: encuentra las consultas lentas antes de optimizar

Antes de tocar nada, necesitas datos. PrestaShop guarda un histórico de consultas lentas si activas el log, pero hay herramientas más directas.

Activar el slow query log de MySQL

# En tu my.cnf o my.ini
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Con long_query_time = 2, toda consulta que tarde más de 2 segundos se registra. En alto tráfico, te sorprenderá la cantidad de queries que superan ese umbral.

Usar el perfilador de PrestaShop

Desde el back office, activa el modo depuración:

// En config/defines.inc.php
define('_PS_MODE_DEV_', true);
define('_PS_DEBUG_PROFILING_', true);

Esto mostrará al pie de cada página el tiempo de ejecución, número de consultas y las queries más lentas. No lo dejes activo en producción salvo en ventanas de mantenimiento controladas.

[WARNING] El profiling en producción puede añadir latencia y exponer información sensible. Actívalo solo durante 5-10 minutos para capturar datos.

Herramientas externas

Percona Toolkit: pt-query-digest analiza logs de consultas lentas y las agrupa por frecuencia y tiempo total.
phpMyAdmin / Adminer: el comando EXPLAIN sobre consultas sospechosas te muestra si están usando índices o haciendo escaneos completos de tabla.

Configuración del motor MySQL para alto tráfico

Muchas optimizaciones empiezan en la configuración del servidor de base de datos. PrestaShop usa InnoDB por defecto, y los parámetros por defecto de MySQL están pensados para entornos pequeños.

Ajustes críticos en my.cnf

[mysqld]
innodb_buffer_pool_size = 4G         # 70-80% de la RAM disponible
innodb_log_file_size = 512M          # Reduce escrituras frecuentes
innodb_flush_log_at_trx_commit = 2   # Rendimiento vs durabilidad
query_cache_type = 0                 # Desactivado en MySQL 8+, pero en 5.7 mejor 0
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 256

¿Por qué estos valores?

innodb_buffer_pool_size es el parámetro más importante: almacena en memoria datos e índices. Si tu base de datos pesa 10 GB y tienes 8 GB de buffer pool, solo un 80% de los datos caben en RAM. El resto requiere lecturas de disco.
innodb_log_file_size grande evita que InnoDB tenga que hacer flush constantemente del log.
query_cache está obsoleto y puede generar contención en escritura. Mejor desactivarlo.

Tuning post-instalación

Usa la herramienta mysqltuner.pl o tuning-primer.sh para obtener recomendaciones específicas. Ejecútala después de 24-48 horas de tráfico real:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

Optimización de consultas SQL específicas de PrestaShop

PrestaShop ejecuta cientos de consultas por página. Algunas son especialmente pesadas. Vamos a ver las más comunes y cómo optimizarlas.

1. Consultas de categorías con subcategorías

PrestaShop usa una tabla category con nleft y nright (modelo nested set). Las consultas que recorren el árbol suelen ser lentas si no hay índices.

-- Consulta original lenta
SELECT c.*, cl.* 
FROM ps_category c
LEFT JOIN ps_category_lang cl ON c.id_category = cl.id_category
WHERE c.nleft BETWEEN 2 AND 10
AND cl.id_lang = 1
AND c.active = 1
ORDER BY c.nleft;

Optimización: Asegurar que los índices cubren las columnas usadas en WHERE y ORDER BY.

ALTER TABLE ps_category ADD INDEX idx_nleft_active (nleft, active);
ALTER TABLE ps_category_lang ADD INDEX idx_category_lang (id_category, id_lang);

[TIP] En PrestaShop 1.7+, la tabla ps_category ya tiene índices, pero a menudo faltan índices compuestos. Verifica con SHOW INDEX FROM ps_category;.

2. Consultas de productos con combinaciones

Las tiendas con muchas variantes (tallas, colores) sufren consultas como:

SELECT p.*, pl.*, sa.*
FROM ps_product p
LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_stock_available sa ON p.id_product = sa.id_product
WHERE p.id_category_default = 123
AND p.active = 1
AND pl.id_lang = 1
ORDER BY p.date_add DESC
LIMIT 20;

Problema: ORDER BY p.date_add con LIMIT fuerza un escaneo completo si no hay índice.

Solución: Crear un índice compuesto que cubra filtro y orden:

ALTER TABLE ps_product ADD INDEX idx_category_date (id_category_default, active, date_add);

Y en consultas con combinaciones, usa STRAIGHT_JOIN para forzar el orden de lectura:

SELECT STRAIGHT_JOIN p.*, pl.*, sa.*
FROM ps_product p
STRAIGHT_JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1
LEFT JOIN ps_stock_available sa ON p.id_product = sa.id_product
WHERE p.id_category_default = 123 AND p.active = 1
ORDER BY p.date_add DESC
LIMIT 20;

3. Consultas de búsqueda con LIKE

Las búsquedas en PrestaShop usan LIKE '%termino%' que no puede usar índices B-tree. Para alto tráfico, necesitas un motor de búsqueda dedicado.

Solución inmediata: Usar índices FULLTEXT en las tablas de idioma:

ALTER TABLE ps_product_lang ADD FULLTEXT idx_search (name, description, description_short);
ALTER TABLE ps_category_lang ADD FULLTEXT idx_search (name, description);

Luego modifica las consultas para usar MATCH ... AGAINST en lugar de LIKE.

Solución definitiva: Implementar Elasticsearch o Algolia. El módulo oficial de PrestaShop para Elasticsearch reduce drásticamente la carga de MySQL.

4. Consultas de módulos de terceros

Muchos módulos añaden consultas sin índices. Por ejemplo, módulos de valoraciones, comparación de productos o listas de deseos.

-- Ejemplo típico de módulo de valoraciones
SELECT AVG(grade) as avg_grade, COUNT(*) as total
FROM ps_product_comment
WHERE id_product = 456 AND validated = 1;

Optimización: Añadir índice compuesto:

ALTER TABLE ps_product_comment ADD INDEX idx_product_validated (id_product, validated);

Estrategia de caching para reducir consultas

No todas las consultas necesitan ejecutarse en cada petición. PrestaShop tiene un sistema de cache basado en tablas, pero podemos mejorarlo.

Cache de consultas con Redis

Instala el módulo de cache Redis para PrestaShop. Configura:

// En config/defines.inc.php
define('_PS_CACHE_ENABLED_', true);
define('_PS_CACHE_TYPE_', 'CacheRedis');

Y en el servidor, asegura que Redis tenga suficiente memoria:

# /etc/redis/redis.conf
maxmemory 2gb
maxmemory-policy allkeys-lru

Cache de resultados de consultas específicas

Para consultas pesadas que no cambian a menudo (ej: categorías principales), usa el método Cache::getInstance()->set():

$cache_key = 'category_tree_'.(int)$id_lang;
if (!$result = Cache::getInstance()->get($cache_key)) {
    $result = Db::getInstance()->executeS('SELECT ...');
    Cache::getInstance()->set($cache_key, $result, 3600); // 1 hora
}

Cache de páginas completas

Para alto tráfico, considera un CDN con cache de páginas (Varnish, CloudFlare) o un plugin de cache como PrestaShop Cache Boost. Esto evita que PHP y MySQL trabajen en cada visita.

Revisión de índices existentes y fragmentación

Con el tiempo, los índices se fragmentan y pierden eficiencia. Programa tareas periódicas:

-- Analizar tablas
ANALYZE TABLE ps_product;
ANALYZE TABLE ps_product_lang;

-- Optimizar tablas (reconstruye índices)
OPTIMIZE TABLE ps_product;
OPTIMIZE TABLE ps_category;

[INFO] OPTIMIZE TABLE bloquea la tabla durante la operación. Ejecútalo en ventanas de bajo tráfico o usa pt-online-schema-change de Percona.

Índices que sobran

Elimina índices duplicados o no utilizados. PrestaShop 1.7 instala muchos índices por defecto que quizá no necesites:

-- Ver índices duplicados
SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics
WHERE table_schema = 'tu_basededatos'
GROUP BY table_name, index_name
HAVING COUNT(*) > 1;

Monitoreo continuo y alertas

La optimización no termina nunca. Implementa monitoreo para detectar nuevas consultas lentas.

Configurar alertas con Prometheus y Grafana

Exporta métricas de MySQL con mysqld_exporter y crea dashboards que muestren:

Consultas lentas por minuto
Tiempo de ejecución promedio
Uso de buffer pool
Conexiones activas

Script de detección temprana

Crea un script que envíe alerta si una consulta tarda más de 5 segundos:

#!/bin/bash
# slow_query_alert.sh
tail -f /var/log/mysql/mysql-slow.log | while read line; do
    if echo "$line" | grep -q "# Query_time: [5-9]"; then
        echo "ALERTA: Consulta lenta detectada" | mail -s "Slow query" admin@tutienda.com
    fi
done

Conclusión: la optimización es un proceso, no un evento

La optimización consultas SQL en PrestaShop para alto tráfico ecommerce requiere un enfoque sistemático: diagnosticar, configurar el motor, optimizar queries, cachear y monitorear. No esperes resultados milagrosos de una sola acción; cada mejora suma.

Recuerda que MySQL en PrestaShop tiene sus particularidades (nested sets, tablas de idiomas, combinaciones). Aplica los cambios en un entorno de staging primero, mide el impacto con herramientas de profiling y despliega gradualmente.

Si tu tienda sigue lenta después de aplicar estas técnicas, considera migrar a un servidor dedicado con MySQL optimizado, o incluso a soluciones como Amazon RDS con read replicas para separar lecturas de escrituras.

[TIP FINAL] La mejor optimización es la que evita consultas innecesarias. Revisa los módulos que tienes instalados: muchos hacen consultas redundantes. Desactiva los que no uses y elige módulos ligeros y bien codificados.