Escolhi este assunto de índices de tabelas para ser o primeiro post da área de informática do site porque quando era desenvolvedor de sistemas desktop para poucos clientes (acessos concorrentes) não dava muita importância para este tema, ou então porque nas organizações onde trabalhava havia uma área específica de DBA (Database Adminstrator) que cuidava dessa parte de "
tunning" de banco de dados.
Porém, quando passei a "trabalhar" (fazemos por hobby e por amor ao Vasco, sem ganhar nada com isso) no SuperVasco, ficamos de cabelos brancos com isso, porque quando havia aquelas notícias bombásticas, chegamos a ter (ainda temos até hoje) 1.000 usuários por minuto acessando o site. Resultado: Eram constantes as quedas de performance do site, quando não acontecia o maldito "
too many connections".
Para ilustrar a importância que se deve dar ao tema, criei um bdteste no MySQL, com duas tabelas: Produtos e Fornecedores. Para efeito de ilustração, suponha que cada produto só pode ser vendido por um fornecedor.
A estrutura das tabelas é simples: Codigo e Nome do produto e Codigo e Nome do fornecedor. Para se recuperar todos os produtos vendidos por um fornecedor, o seguinte comando é executado:
s.elect p.strNomeproduto, f.strNomeFornecedor from produtos p join fornecedores f on f.lngCodFornecedor = p.lngCodFornecedor where f.lngCodFornecedor = 2
Executar o explain (comando do MySQL que informa como o o mecanismo do banco irá executar consultas) nos mostra o seguinte resultado:
id | s.elect_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|
1 | SIMPLE | f | const | PRIMARY | PRIMARY | 4 | const | 1 | (null) |
1 | SIMPLE | p | ALL | | | | | 4 | Using where |
Explicando o explainTrês indicativos de que algo não vai bem:
- O valor ALLna coluna type
- Os campos Possible keys e keys estarem com valor nulo
Isso indica que não será usado
nenhum índice para varrer a tabela p (produtos). Ou seja, independente do que você quiser executar, o MySQL vai varrer a tabela
toda. Nesse exemplo, o estrago parece não ter sido muito grande, porque foram varridos só
quatro registros, multiplicadas as duas tabelas. Mas há que se considerar que a tabela p tinha
quatro registros, ou seja, foram varridos
todos os registros dessa tabela.
Imaginaram o estrago se essa tabela tivesse 10.000 itens? Como a quantidade de registros é produto cartesiano entre as tabelas, o valor total de registros é a multiplicação das linhas desse explain. Ou seja, se houvesse 50 fornecedores, ele teria varrido 50 vezes 10.000 registros.
Ok, mas como resolver isso?Criando uma
foreign key para a tabela de fornecedor na tabela de produtos . Assim, independente do número de produtos, o motor do banco de dados varrerá, na tabela de produtos, apenas o fornecor cujo código satisfaça à condição colocada no "
where".
Vejam como ficou a tabela, após a criação da
foreign key:
id | s.elect_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|
1 | SIMPLE | f | const | PRIMARY | PRIMARY | 4 | const | 1 | (null) |
1 | SIMPLE | p | ref | idxFornecedor | idxFornecedor | 4 | const | 2 | (null) |
Notem que os campos
possible_keys e
key (chave que o MySQL entendeu por bem usar) estão preenchidos e que o número de registros varridos caiu de 4 para 2.
Para finalizar, as dicas que dou para o tema são as seguinte:
Estude bem o seu modelo relacional e
teste a performance das suas consultas antes de sentar no ASP ou no PhP. Depois, pode ser como trocar um pneu de carro com ele em movimento...

Outra coisa: O exemplo dado é para MySQL, mas o mesmo princípio vale para qualquer banco de dados, porque isso é baseado em teoria de conjuntos, base para todos os bancos de dados.
Espero que essa dica tenha sido útil!Abraços!