SELECT que funciona com 10 linhas e trava com 50 mil: limite, filtro e índice

No tutorial, a tabela tinha cinco alunos e a listagem abria na hora. No sistema da loja, a mesma consulta roda sobre 50 mil pedidos. A página leva 12 segundos, o PHP estoura o memory_limit numa sexta de promoção e o navegador do atendente trava tentando desenhar uma tabela HTML com 50 mil linhas. O código não mudou. Mudou o tamanho do mundo.

A resposta curta: peça ao banco só a página que a tela vai mostrar. Selecione as colunas necessárias, filtre com WHERE, ordene com ORDER BY e limite com LIMIT, tudo no SQL, com os valores passados como parâmetro. Depois crie um índice que atenda ao filtro e à ordem, e confira com EXPLAIN que o banco está usando esse índice.

Por que a consulta do iniciante não escala

O exemplo clássico de tutorial é este:

<?php
$resultado = mysqli_query($con, "SELECT * FROM pedidos");
while ($linha = mysqli_fetch_assoc($resultado)) {
    echo "<tr><td>" . $linha['id'] . "</td><td>" . $linha['cliente_nome'] . "</td></tr>";
}

Com cinco linhas, ele ensina bem o caminho do dado. Com 50 mil, cada etapa cobra o preço:

• O banco lê a tabela inteira. Sem filtro nem limite, não há atalho: todas as linhas e todas as colunas, inclusive textos longos que a tela não mostra.
• A rede leva tudo. Megabytes saem do MySQL para o PHP a cada abertura da página.
• O PHP guarda tudo na memória. Por padrão, as extensões do MySQL no PHP trazem o resultado inteiro para a memória antes de você ler a primeira linha. O memory_limit padrão do PHP é 128 MB, e uma tabela larga chega lá.
• O navegador desenha tudo. Uma tabela HTML com dezenas de milhares de linhas deixa a página lenta até para rolar.

E a pessoa que usa a tela só olha os 50 pedidos mais recentes. Todo o resto foi trabalho jogado fora. Esse exemplo também concatena dados no HTML sem escapar, o que abre espaço para script injetado pelo nome do cliente; vamos corrigir isso também.

Como trazer só uma página com PDO

1. Liste as colunas que a tela mostra e selecione só elas.
2. Leia o número da página e o filtro da URL, convertendo e validando.
3. Monte a consulta com WHERE, ORDER BY estável e LIMIT com OFFSET, usando parâmetros.
4. Peça uma linha a mais que o tamanho da página para saber se existe próxima.
5. Escape a saída no HTML.
6. Trate o erro sem mostrar SQL para o usuário.

<?php
// pedidos.php
$pdo = new PDO(
    'mysql:host=localhost;dbname=loja;charset=utf8mb4',
    getenv('DB_USER'),
    getenv('DB_PASS'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$porPagina = 50;
$pagina = max(1, (int) ($_GET['pagina'] ?? 1));
$offset = ($pagina - 1) * $porPagina;

$statusPermitidos = ['pago', 'pendente', 'cancelado'];
$status = $_GET['status'] ?? 'pago';
if (!in_array($status, $statusPermitidos, true)) {
    $status = 'pago';
}

$sql = 'SELECT id, cliente_nome, total_centavos, criado_em
        FROM pedidos
        WHERE status = :status
        ORDER BY criado_em DESC, id DESC
        LIMIT :limite OFFSET :offset';

try {
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':status', $status);
    $stmt->bindValue(':limite', $porPagina + 1, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $pedidos = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    error_log('Listagem de pedidos: ' . $e->getMessage());
    http_response_code(500);
    exit('Não foi possível carregar os pedidos agora.');
}

$temProxima = count($pedidos) > $porPagina;
$pedidos = array_slice($pedidos, 0, $porPagina);

foreach ($pedidos as $p) {
    printf(
        '<tr><td>%d</td><td>%s</td><td>R$ %s</td><td>%s</td></tr>',
        $p['id'],
        htmlspecialchars($p['cliente_nome'], ENT_QUOTES, 'UTF-8'),
        number_format($p['total_centavos'] / 100, 2, ',', '.'),
        htmlspecialchars($p['criado_em'], ENT_QUOTES, 'UTF-8')
    );
}

if ($temProxima) {
    printf('<a href="?status=%s&amp;pagina=%d">Próxima página</a>',
        urlencode($status), $pagina + 1);
}

Alguns detalhes que fazem esse código funcionar de verdade:

• PDO::PARAM_INT no LIMIT. Se você passar os valores direto no execute([...]), o PDO, com a emulação de prepared statements ativa (o padrão no MySQL), envia os números entre aspas, e o MySQL recusa LIMIT '51' com erro de sintaxe. O bindValue com tipo inteiro resolve.
• Ordem estável. Dois pedidos podem ter o mesmo criado_em. Sem o id como desempate, o mesmo pedido pode aparecer em duas páginas ou em nenhuma.
• Uma linha a mais. Pedir 51 e mostrar 50 diz se existe próxima página sem rodar um COUNT(*) sobre a tabela inteira a cada clique.
• Dinheiro em centavos. O total vem como inteiro e só vira texto formatado na tela, como explica o guia de dinheiro em centavos.

O status vem de uma lista fixa e o resto vai como parâmetro, então nada do que o usuário digita é colado no SQL. A mesma lógica em Node.js está em Node.js e MySQL com parâmetro.

O índice que faz o LIMIT ser rápido

O LIMIT reduz o que volta, mas não garante que o banco leu pouco. Sem índice, para achar os 50 pedidos pagos mais recentes, o MySQL ainda percorre a tabela, filtra e ordena tudo antes de cortar. Confira com EXPLAIN:

EXPLAIN SELECT id, cliente_nome, total_centavos, criado_em
FROM pedidos
WHERE status = 'pago'
ORDER BY criado_em DESC, id DESC
LIMIT 51;

Sem índice, a saída mostra type igual a ALL (leitura da tabela inteira), rows perto do total de linhas e Using filesort na coluna Extra, sinal de que a ordenação foi feita à parte. Crie um índice que comece pelo filtro e siga pela ordem:

CREATE INDEX idx_pedidos_status_data ON pedidos (status, criado_em, id);

Rode o EXPLAIN de novo. A coluna key deve mostrar idx_pedidos_status_data, o número de rows estimado cai e o Using filesort desaparece: o banco caminha pelo índice já na ordem certa e para quando tem 51 linhas.

Quando o OFFSET começa a pesar

Para pular para a página 800, o banco ainda precisa passar pelas 40 mil linhas anteriores e descartá-las. Para listagens de atendimento, em que quase ninguém passa da página 5, o OFFSET é suficiente. Para rolagem infinita, exportação ou tabelas que recebem inserções o tempo todo, use paginação por cursor, descrita em paginação que não pula. E para escolher melhor as colunas, veja consultar só o que a tela precisa.

Erros comuns

• Trazer tudo e paginar no PHP com array_slice. A tela mostra 50 linhas, mas o banco, a rede e a memória pagaram por 50 mil.
• Concatenar o número da página no SQL. Um valor como 1; DROP TABLE não deveria chegar perto da consulta; converta e use parâmetro.
• Passar o LIMIT como texto no execute. O MySQL devolve erro de sintaxe e a listagem quebra.
• ORDER BY sem desempate. Registros repetem ou somem entre páginas.
• COUNT(*) da tabela inteira a cada página. Em tabela grande, contar custa quase tanto quanto listar.
• Mostrar a mensagem do PDOException na tela. O usuário vê nome de tabela e de coluna, e quem quer atacar ganha um mapa.

Checklist

• A consulta seleciona só as colunas que a tela mostra.
• Filtro, ordem e limite estão no SQL, não no PHP.
• Os valores da URL são convertidos, validados e passados como parâmetro.
• LIMIT e OFFSET usam PDO::PARAM_INT.
• O ORDER BY termina numa coluna única.
• O EXPLAIN mostra o índice em uso e nenhum Using filesort.
• A saída é escapada com htmlspecialchars.
• Erros vão para o log, e o usuário vê uma mensagem genérica.

Faça o teste no seu ambiente de homologação: multiplique por cem as linhas da tabela com dados fictícios e abra a listagem. Ela continua abrindo em menos de um segundo?