🔍 Optimizacion de queries SQL — rendimiento avanzado

<?php

// ── Activar _PS_DEBUG_SQL_ para ver todas las queries ──
// En config/config.inc.php (solo en desarrollo):
define('_PS_DEBUG_SQL_', true);
// Las queries aparecen en el Symfony Profiler y en la consola

// ── Medir el tiempo de una query especifica ──
$start  = microtime(true);
$result = Db::getInstance()->executeS('SELECT ...');
$elapsed = microtime(true) - $start;
if ($elapsed > 0.1) { // > 100ms
    PrestaShopLogger::addLog(
        'Slow query: ' . round($elapsed * 1000, 2) . 'ms',
        2, null, 'MyModule'
    );
}

// ── MySQL Slow Query Log ──
// En my.cnf:
// slow_query_log = 1
// slow_query_log_file = /var/log/mysql/slow.log
// long_query_time = 0.5  # Queries > 500ms

// ── EXPLAIN para analizar el plan de ejecucion ──
$explain = Db::getInstance()->executeS(
    'EXPLAIN SELECT p.id_product, pl.name FROM ps_product p
    LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1
    WHERE p.active = 1'
);
// Buscar: type='ALL' (table scan), Extra='Using filesort' (ordenacion lenta)

<?php

// ═══════════════════════════════════════════════
// ❌ N+1 QUERIES — Patron comun en hooks de listado
// ═══════════════════════════════════════════════

public function hookDisplayProductListItem(array $params): string
{
    $product = $params['product'];

    // ❌ N queries — una por producto en el listado
    $reviews = Db::getInstance()->executeS(
        'SELECT * FROM ps_mymodule_review WHERE id_product = ' . (int) $product['id_product']
    );

    // Si el listado tiene 40 productos = 40 queries adicionales!
    return $this->display(__FILE__, 'product-reviews.tpl');
}

// ══════════════════════════════════════════════
// ✅ SOLUCION — Pre-cargar datos en un solo batch
// ══════════════════════════════════════════════

public function hookActionProductListOverride(array &$params): void
{
    // Este hook recibe TODOS los productos del listado a la vez
    $products = $params['products'];
    $ids      = array_column($products, 'id_product');

    // UNA sola query para todos los productos
    $reviewsRaw = Db::getInstance()->executeS(
        'SELECT id_product, COUNT(*) as count, AVG(rating) as avg_rating
        FROM ps_mymodule_review
        WHERE id_product IN (' . implode(',', array_map('intval', $ids)) . ')
        GROUP BY id_product'
    ) ?: [];

    // Indexar por id_product para acceso O(1)
    $reviews = array_column($reviewsRaw, null, 'id_product');

    // Asignar a cada producto
    foreach ($params['products'] as &$product) {
        $product['mymodule_reviews'] = $reviews[$product['id_product']] ?? [
            'count' => 0, 'avg_rating' => 0
        ];
    }
}

-- ── Regla de los 3 pasos para indices ──
-- 1. Indices en columnas WHERE
-- 2. Indices en columnas JOIN
-- 3. Indices en columnas ORDER BY

-- ── Indice simple ──
ALTER TABLE `ps_mymodule_item` ADD INDEX `idx_active` (`active`);

-- ── Indice compuesto (columnas de mayor a menor selectividad) ──
ALTER TABLE `ps_mymodule_item`
    ADD INDEX `idx_active_category_sort` (`active`, `id_category`, `sort_order`);
-- Cubre la query: WHERE active=1 AND id_category=5 ORDER BY sort_order

-- ── Indice de cobertura (covering index) ──
-- Incluye todas las columnas que necesita la query
-- Evita acceso a la tabla principal (Using index en EXPLAIN)
ALTER TABLE `ps_mymodule_item`
    ADD INDEX `idx_cover` (`active`, `id_category`, `price`, `id_mymodule_item`);
-- Esta query NO necesita acceder a la tabla:
-- SELECT id_mymodule_item, price FROM ps_mymodule_item
-- WHERE active=1 AND id_category=5

-- ── Ver indices actuales ──
SHOW INDEX FROM `ps_mymodule_item`;

-- ── Eliminar indice duplicado o innecesario ──
ALTER TABLE `ps_mymodule_item` DROP INDEX `idx_redundante`;

<?php

// ── Paginacion clasica con OFFSET (lenta en paginas altas) ──
// OFFSET 10000 = MySQL lee 10000 filas y descarta las primeras
$page   = 500; // Pagina 500
$limit  = 20;
$offset = ($page - 1) * $limit; // OFFSET 9980 — muy lento

$sql = 'SELECT * FROM ps_product WHERE active=1 ORDER BY id_product LIMIT ' . $limit . ' OFFSET ' . $offset;

// ── Paginacion con cursor (rapida incluso en paginas altas) ──
// En lugar de OFFSET, usar el ultimo ID de la pagina anterior
$lastId = (int) Tools::getValue('last_id', 0);

$query = (new DbQuery())
    ->select('id_product, reference, price')
    ->from('product')
    ->where('active = 1')
    ->where($lastId > 0 ? 'id_product > ' . $lastId : '1=1')
    ->orderBy('id_product ASC')
    ->limit($limit);

$products = Db::getInstance()->executeS($query) ?: [];
$nextCursor = !empty($products) ? end($products)['id_product'] : null;

// El cliente pasa ?last_id={nextCursor} para la siguiente pagina
// Funciona en tiempo constante independientemente de la pagina

<?php

// ── Si hay un servidor esclavo (replica) configurado en PS ──
// BO → Parametros Avanzados → Base de datos → Servidores de la BD

// Obtener la instancia de lectura (esclavo si disponible)
$dbSlave  = Db::getInstance(_PS_USE_SQL_SLAVE_);

// Usar el esclavo para queries de lectura pesadas
$products = $dbSlave->executeS(
    (new DbQuery())
        ->select('p.id_product, pl.name, p.price')
        ->from('product', 'p')
        ->leftJoin('product_lang', 'pl', 'p.id_product = pl.id_product AND pl.id_lang = 1')
        ->where('p.active = 1')
        ->orderBy('p.date_add DESC')
        ->limit(100)
);

// ── SIEMPRE usar el maestro para escrituras ──
$dbMaster = Db::getInstance(); // Sin flag = maestro
$dbMaster->insert('mymodule_log', ['message' => pSQL('Query completed')]);

// ── Si no hay esclavo, getInstance() devuelve el maestro de todas formas ──
// El codigo es el mismo — no hay que detectar si hay esclavo