Quando acrescentar um índice torna tudo mais lento

A tabela dos números diários tem uma chave: o contador e a data juntos. Cinco pontos do código fazem-lhe outra pergunta — não «este contador ao longo do tempo», mas «todos os contadores nestes dias». A galeria da página inicial, a lista dos melhores, o mapa do sítio, um gráfico de 90 dias e os números de estado. Sem um índice na coluna da data, cada uma delas lê as 114.113 linhas.

Esse índice torna a galeria dez vezes mais rápida. Não foi acrescentado. Aqui está a medição que o decidiu.

Uma consulta ficou muito mais rápida

A galeria pede os contadores que hoje tiveram visitas. Sem índice na data percorre a tabela inteira; com índice procura uma única data e lê 443 linhas. De 61,5 milissegundos passou-se a 6,1. Não é um resultado subtil, e se tivesse sido a única coisa medida, o índice já estaria na base de dados.

Duas consultas ficaram mais lentas

O mapa do sítio pede catorze dias e ficou 19 % mais lento. Os números de estado pedem um ano e ficaram 10 % mais lentos. Nos dois casos o planeador passou da chave primária para o índice novo e escolheu pior.

Esta é a parte que vale a pena perceber, porque não é uma falha do planeador. Ler um intervalo através de um índice secundário significa encontrar as linhas correspondentes no índice e depois ir buscar cada uma à tabela. Para uma fatia estreita é uma pechincha. Para catorze dias em treze meses continuam a ser muitas linhas buscadas uma a uma, e uma passagem directa pela tabela — lida por ordem física, que é o que discos e caches gostam — ganha. O planeador não sabe disso. Vê um intervalo e um índice, e leva-o.

Não se pode dizer «usa este índice só para a galeria». Um índice está disponível para qualquer consulta que toque essa coluna, e o planeador usá-lo-á onde as suas estimativas disserem. Acrescentar um é uma alteração a todas as consultas dessa tabela, não àquela que se tinha em mente.

Ao todo: nada

Nas cinco somadas, 555 milissegundos passaram a 524. Uma melhoria de 1,1×, que num Raspberry Pi que serve estas páginas a partir de uma cache de qualquer forma não vale uma alteração de esquema.

O gráfico de 90 dias parece ter melhorado 9 %. Não é contado, e vale a pena dizer porquê. Cada consulta foi medida três vezes: sem o índice, com ele, e outra vez sem. Se a segunda passagem sem índice não cair perto da primeira, a diferença foi a máquina e não a alteração. No gráfico de 90 dias as duas passagens sem índice diferiam 13,8 % — mais do que o efeito que se afirmaria. Portanto essa linha não mede nada, e como nada é relatada.

Como foi medido, e porque isso importa

Não na tabela em funcionamento. O script faz uma cópia, trabalha na cópia e apaga-a no fim. Acrescentar um índice a uma tabela de produção para ver o que acontece é coisa que só se pode fazer uma vez por surpresa.

As consultas são as verdadeiras, tiradas do código, junção com a tabela dos contadores incluída. Uma versão anterior deste teste usava um substituto simplificado e dava uma resposta muito mais entusiasmante: vinte vezes mais rápido. Essa consulta simplificada não é executada por nada.

Que número teria saído

Se se tivesse medido da maneira corrente — pegar na consulta que parece lenta e cronometrá-la antes e depois — a resposta teria sido «dez vezes mais rápido, avança». O índice teria entrado, a galeria teria acelerado, o mapa do sítio e a página de estado teriam abrandado, e ninguém teria ligado as duas coisas, porque ninguém estava a olhar para elas.

É esta a forma geral da coisa. Uma alteração a infra-estrutura partilhada não se avalia pelo caso que a motivou. A pergunta não é «isto ajuda aquilo para que estou a olhar» mas «que mais é que isto toca, e o que lhe acontece».

Há uma versão que poderia resultar: um índice a levar as duas colunas, a data primeiro, de modo que a galeria possa ser respondida só a partir do índice, sem voltar à tabela. Talvez também não tentasse o planeador nos intervalos largos. Isso não está medido, por isso não é uma recomendação — é a próxima coisa a pôr na cópia.