Quando uma consulta no MySQL utiliza um índice, mas ainda apresenta alto tempo de resposta, o problema geralmente não está na ausência do índice, mas em como ele foi projetado, como a SQL foi escrita ou em limitações estruturais e de configuração. As causas podem ser divididas em seis categorias principais: falhas no design dos índices, más práticas na escrita de SQL, distribuição desigual dos dados, estrutura de tabela ineficiente, configurações de banco de dados inadequadas e gargalos de hardware.

1. Falhas no Design dos Índices

Seletividade Baixa

A seletividade de um índice determina sua eficácia. Quanto maior a proporção de valores únicos, melhor o índice filtra os dados. Se um índice possui baixa seletividade, o otimizador pode ignorá-lo e optar por uma varredura completa da tabela.

-- Calculando a seletividade de uma coluna
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS seletividade
FROM orders;

-- Se o resultado for próximo de 0 (ex: 0.000005), a coluna 'status' não é um bom candidato para índice isolado.

Violação do Prefixo Mais à Esquerda

Índices compostos devem respeitar a regra do prefixo mais à esquerda. A ordem das colunas no índice é crucial para que o otimizador consiga utilizá-lo de forma eficiente.

-- Criação de um índice composto
CREATE INDEX idx_dept_role_status ON employees(department_id, role_id, status);

-- ✅ Utiliza o índice (respeita o prefixo)
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM employees WHERE department_id = 10 AND role_id = 5;

-- ❌ Ignora o índice (quebra o prefixo)
SELECT * FROM employees WHERE role_id = 5;
SELECT * FROM employees WHERE status = 'ACTIVE';

-- ⚠️ Utiliza parcialmente (apenas a primeira coluna é indexada)
SELECT * FROM employees WHERE department_id = 10 AND status = 'ACTIVE';

Índices Redundantes

Manter índices que já estão cobertos por outros índices compostos aumenta a sobrecarga de escrita e consome espaço em disco desnecessariamente.

-- ❌ Exemplo de redundância
CREATE INDEX idx_dept ON employees(department_id);
CREATE INDEX idx_dept_role ON employees(department_id, role_id);
-- O índice 'idx_dept' é redundante, pois 'idx_dept_role' já atende consultas que filtram apenas por 'department_id'.

-- ✅ Correção
DROP INDEX idx_dept ON employees;

2. Más Práticas na Escrita de SQL

Uso de SELECT * e Retorno à Tabela (Table Lookup)

Selecionar todas as colunas força o banco de dados a buscar os dados completos na tabela principal (clustered index) após encontrar os registros no índice secundário. Isso gera muitas operações de I/O aleatório.

-- ❌ Lento: Exige retorno à tabela para buscar todas as colunas
SELECT * FROM orders WHERE customer_id = 100;

-- ✅ Rápido: Usa índice cobrindo (Covering Index), evitando o retorno à tabela
-- Assumindo que exista o índice idx_cust_date(customer_id, order_date)
SELECT customer_id, order_date FROM orders WHERE customer_id = 100;

Padrões LIKE que Invalidam Índices

Usar curingas no início de uma string impede o uso do índice B-Tree.

-- ✅ Utiliza o índice
SELECT * FROM products WHERE product_name LIKE 'Smart%';

-- ❌ Ignora o índice (varredura completa)
SELECT * FROM products WHERE product_name LIKE '%Smart';
SELECT * FROM products WHERE product_name LIKE '%Smart%';

-- Alternativas: Usar índices de texto completo (FULLTEXT) ou ferramentas externas como Elasticsearch para buscas complexas.

Funções e Cálculos em Colunas Indexadas

Aplicar funções ou operações matemáticas diretamente na coluna indexada impede o otimizador de usar o índice.

-- ❌ Ignora o índice
SELECT * FROM transactions WHERE DATE(transaction_date) = '2023-10-01';
SELECT * FROM transactions WHERE amount * 1.1 > 100;

-- ✅ Utiliza o índice
SELECT * FROM transactions WHERE transaction_date >= '2023-10-01' AND transaction_date < '2023-10-02';
SELECT * FROM transactions WHERE amount > 100 / 1.1;

Conversão Implícita de Tipos

Se o tipo de dado na cláusula WHERE não corresponder exatamente ao tipo da coluna, o MySQL realizará uma conversão implícita, ivnalidando o índice.

-- Supondo que 'document_number' seja do tipo VARCHAR
CREATE INDEX idx_doc ON users(document_number);

-- ❌ Ignora o índice (comparação de string com número)
SELECT * FROM users WHERE document_number = 123456789;

-- ✅ Utiliza o índice
SELECT * FROM users WHERE document_number = '123456789';

3. Distribuição e Volume de Dados

Volume Excessivo de Dados

Tabelas com bilhões de registros podem tornar a travessia da árvore B-Tree e o retorno à tabela muito lentos, mesmo com índices. Estratégias de mitigação incluem: particionamento de tabelas, sharding (divisão horizontal), arquivamento de dados históricos e uso rigoroso de índices cobertos.

Distribuição Desproporcional (Data Skew)

Se um valor específico domina a tabela, o otimizador pode decidir que um varredura completa é mais rápida do que usar o índice e fazer o retorno à tabela.

-- Verificando a distribuição
SELECT region, COUNT(*) as total FROM sales GROUP BY region;
-- Se 95% das vendas são da região 'SOUTH', o índice em 'region' será inútil para essa região.

4. Estrutura da Tabela

Linhas Muito Longas e Campos Grandes

Colunas do tipo TEXT ou BLOB aumentam o tamanho da linha, reduzindo a quantidade de registros que cabem em uma página de dados, o que aumenta o I/O.

-- ❌ Estrutura ineficiente
CREATE TABLE blog_posts (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    author VARCHAR(100),
    content LONGTEXT
);

-- ✅ Estrutura otimizada (Particionamento Vertical)
CREATE TABLE blog_posts (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    author VARCHAR(100)
);

CREATE TABLE blog_post_contents (
    post_id INT PRIMARY KEY,
    content LONGTEXT,
    FOREIGN KEY (post_id) REFERENCES blog_posts(id)
);

5. Configurações do Banco de Dados

Tamanho do Buffer Pool

O InnoDB Buffer Pool armazena dados e índices em memória. Se for muito pequeno, o banco dependerá excessivamente do disco.

-- Verificar o tamanho atual
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Recomenda-se alocar entre 70% e 80% da memória RAM total do servidor dedicado ao banco.
SET GLOBAL innodb_buffer_pool_size = 17179869184; -- Exemplo: 16GB

6. Análise do Plano de Execução

A ferramenta fundamental para diagnosticar lentidão é o comando EXPLAIN.

EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 100;

Ao analisar a saída, foque nos seguintes campos:

• type: Indica o tipo de acesso. Valores como ALL (varredura completa) ou index (varredura de índice) são ruins. O ideal é ref, eq_ref, const ou range.
• rows: Estimativa de linhas que o MySQL acredita que precisará examinar. Quanto menor, melhor.
• Extra: Procure por Using filesort (ordenação em disco/memória fora do índice) e Using temporary (uso de tabelas temporárias), pois são fortes indicadores de gargalos de performance.