Índices de Múltiplas Colunas ASC e DESC, faz diferença?

Olá pessoal! Tudo bem?

Alguns estiveram conosco lá no IMasters Developer Week em Vitória e foi muito bacana! Obrigado ao pessoal da IMasters por todo apoio, PH e Tiago pela oportunidade. Muito obrigado também à Móveis Simonetti por todo apoio para que pudéssemos estar naquele evento nas pessoas do nosso diretor de TI e de nosso presidente.

No último post, tivemos uma ótima pergunta do giqu1eu sobre índices.

Algumas coisas aconteceram e acabei não respondendo logo à pergunta no POST, então gerei esse aqui só pra dar a resposta.

Para quem não se lembra, vamos à pergunta e então à resposta completinha:

#########################

“giqu1eu28 de fevereiro de 2015 18:06”

Olá Marcelo, tdo bem? Comecei acompanhar seu blog, obrigado pelo material, dicas e afins que estão disponíveis no blog!

Um certo dia li em algum lugar que não é recomentado criar no mesmo Indice duas colunas com declaração de “Order” diferentes:

INDEX `w1` (`data` ASC, `valor` DESC))

sendo o recomendado que tanto DATA como VALOR sejam da mesma ordem, ASC ou DESC:

INDEX `w1` (`data` ASC, `valor` ASC)) ou INDEX `w1` (`data` DESC, `valor` DESC)).

Isso procede? Qual o impacto par o otimizador?

[]’s

Essa pergunta foi bem interessante, nunca havia pensado nessa possibilidade.

Cortando a própria CARNE

Então pesquisei o material do meu banco predileto (MySQL) na base Oracle de conhecimento. E só encontrei a parte de como o banco usa o índice. O que acabou gerando uma pequena errata em relação ao último POST, em relação ao uso de índices. Então vamos logo esclarecendo (aqui é que vai doer na carne) que o índice não será usado para ordenação, apenas para busca. Ou seja, apenas a cláusula WHERE ou o ON do JOIN utilizará o índice. Resumindo o índice é usado para encontrar o que queremos e não para ordenar o que achamos. A ordenação ocorre no buffer de ordenação do seu banco.

Dito isto, vamos à resposta do GIGU: Quando criamos um índice com duas colunas, o índice armazenará o endereço primeiro com a primeira coluna do índice, na ordem em que definirmos. E Então para cada valor da primeiro coluna irá armazenar o endereço de cada linha de registro da segunda coluna do índice, na ordem em que definimos. É por isso que o índice de duas colunas substitui o mesmo índice apenas com a primeira coluna daquele mesmo índice.

Como normalmente que usamos a cláusula WHERE ou JOIN, não estamos interessados na ordem, mas em uma faixa de valores ou valores específicos NÃO FAZ DIFERENÇA em relação ao número de registros.

Porém, vale ressaltar que, em geral, criamos índices na ordem normal (ASCendente e não DESCencente)

Extrapolando agora as possibilidades

Mas, voltando ao exemplo da tabela VENDA, pensei na seguinte possibilidade e gostaria de ter a resposta de vocês. Se um índice pode ter ordem, ASC ou DESC, e eu criar um índice DESC com a data de venda, vocês concordam que no início do índice teremos primeiro os endereços dos registros das últimas vendas (a não ser é claro casos de vendas inseridas depois, etc)??? E como normalmente temos mais interesse em registros recentes do que em registros antigos, faz mais sentido montar índices de campos de data ordenados de forma DESCendente, correto?

Eu peguei a tabela Montagens utilizada na palestra do DevWeekVix e fiz essa experiência. Criei primeiro um índice com data de montagem ASCendente, e busca registros recentes. A busca levou 46 milésimos de segundo para recuperar 8mil registros. Com o índice DESCendente a mesma busca levou 37 milésimos de segundo. O número de linhas percorridas foi exatamente o MESMO.

Então faz diferença criar ASC ou DESC???

Não aceito ainda essa como a resposta definitiva. Por isso, prometo fazer mais testes com esse tipo de índice com campo DATE, Inteiro e datetime e trazer novidades no próximo POST no fim de Abril, OK?

Abração a Todos e até a próxima!
Seu amigo, Marcelo Raposo!

[Crédito da Imagem: Índice de Múltiplas Colunas – ShutterStock]