Todo mundo aprende que índice acelera consulta. O que poucos aprendem é que índice mal planejado pode até piorar performance de escrita, e que existe uma diferença enorme entre "ter um índice" e "ter o índice certo" para a consulta que você realmente executa.
Índice composto na ordem certa
Um índice em duas colunas não é equivalente a dois índices separados — a ordem das colunas no índice composto importa, e deve seguir a ordem em que as cláusulas aparecem no WHERE da consulta mais frequente. Já vi índice criado na ordem errada que nunca era usado pelo otimizador de consulta, ocupando espaço e ainda desacelerando cada escrita, sem entregar nenhum ganho de leitura.
O custo invisível do índice
Cada índice adicionado acelera leitura mas desacelera escrita, porque o banco precisa atualizar o índice a cada INSERT ou UPDATE. Em tabelas com muita escrita e pouca leitura, excesso de índice é tão problema quanto falta de índice — a mesma armadilha de modelagem que descrevo em erros de modelagem MySQL.
Quando o problema não é o banco, é a arquitetura
Chega um ponto em que nenhum índice resolve, porque o problema real é um monólito fazendo todo o trabalho pesado sozinho. É aí que decido se vale a pena escalar diferente — o que discuto em quando um monólito ainda aguenta escala.
