Manual de Referência do MySQL :: 5.4.3 Como o MySQL Utiliza Índices

Os índices são utilizados para encontrar registros com um valor específico de uma coluna rapidamente. Sem um índice o MySQL tem de iniciar com o primeiro registro e depois ler através de toda a tabela até que ele encontre os registros relevantes. Quanto maior a tabela, maior será o custo. Se a tabela possui um índice para as colunas em questão, o MySQL pode rapidamente obter uma posição para procurar no meio do arquivo de dados sem ter que varrer todos os registros. Se uma tabela possui 1000 registros, isto é pelo menos 100 vezes mais rápido do que ler todos os registros sequencialmente. Note que se você precisar acessar quase todos os 1000 registros, seria mais rápido acessá-los sequencialmente porque evitaria acessos ao disco.

Todos os índices do MySQL (PRIMARY, UNIQUE e INDEX) são armazenados em árvores B. Strings são automaticamente compactadas nos espaços finais e prefixados. See Secção 6.5.7, “Sintaxe CREATE INDEX”.

Índices são utilizados nos seguintes modos:

Suponha que você utilize a seguinte instrução SELECT:

mysql> SELECT * FROM nome_tabela WHERE col1=val1 AND col2=val2;

Se um índice de colunas múltiplas existir em col1 e col2, os registros apropriados podem ser recuperados diretamente. Se índices separados de únicas colunas existirem em col1 e col2, o otimizador tentará encontrar o índice mais restritivo decidindo qual índice irá encontrar menos registros e usará este índice para recuperar os registros.

Se a tabela possuir um índice de múltiplas colunas, qualquer prefixo mais à esquerda do índice pode ser usado pelo otimizador para encontrar registros. Por exemplo, se você possui um índice de três colunas em (col1, col2, col3), você tem capacidades de busca indexada em (col1), (col1, col2) e (col1, col2, col3).

O MySQL não pode utilizar um índice parcial se as colunas não formarem um prefixo mais à esquerda do índice. Suponha que você tenha as instruções SELECT mostradas abaixo:

mysql> SELECT * FROM nome_tabela WHERE col1=val1;
mysql> SELECT * FROM nome_tabela WHERE col2=val2;
mysql> SELECT * FROM nome_tabela WHERE col2=val2 AND col3=val3;

Se um índice existir em (col1, col2, col3), somente a primeira consulta anteriores utiliza o índice. A segunda e terceira consultas involvem colunas indexadas, mas (col2) e (col2, col3) não são os prefixos mais à esquerda de (col1, col2, col3).

O MySQL também utiliza índices para comparações do tipo LIKE se o argumento para LIKE for uma string constante que não inicie com um meta caracter Por exemplo as seguintes instruções SELECT utilizam índices:

mysql> SELECT * FROM nome_tbl WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM nome_tbl WHERE key_col LIKE "Pat%_ck%";

Na primeira instrução, somente os registros com "Patrick" <= key_col < "Patricl" são considerados. Na segunda instrução, somente registros com "Pat" <= key_col < "Pau" são considerados.

As seguintes instruções SELECT não usarão índices:

mysql> SELECT * FROM nome_tbl WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM nome_tbl WHERE key_col LIKE other_col;

Na primeira instrução, o valor LIKE inicia com um meta caracter. Na segunda instrução, o valor LIKE não é uma constante.

O MySQL 4.0 faz outra otimização em LIKE. Se você usar ... LIKE "%string%" e string tiver mais de 3 caracteres, o MySQL usará o algorítmo Turbo Boyer-Moore para inicializar o padrão para a string e então usar este padrão para realizar a pesquisa mais rápido.

Buscas usando nome_coluna IS NULL usa índices se nome_coluna é um índice.

O MySQL normalmente utiliza o índice que encontra o menor número de registros. Um índice é usado para colunas que você compara com os seguintes operadores: =, >, >=, <, <=, BETWEEN ou um LIKE com um padrão que começa com um prefixo sem meta caracteres como 'algo%'.

Qualquer índice que não cobrem todos os níveis de AND na cláusula WHERE não é utilizado para otimizar a consulta. Em outras palavras: Para poder usar um índice, um prefixo do índice deve ser utilizado em todo agrupamento AND.

A seguinte cláusula WHERE utilizará índices:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimised like "index_part1='hello'" */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
          /* Can use index on index1 but not on index2 or index 3 */

Estas cláusulas WHERE não utilizam índices:

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* Index is not used in
                                                        both AND parts */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows  */

Perceba que algumas vezes o MySQL não utilizará um índice, mesmo se algum estiver disponível. Um exemplo deste caso é quando o uso do índice necessita que o MySQL acesse mais de 30% dos registros na tabela. (Neste caso uma varredura da tabela é provavelmente mais rápido, já que ela necessitará de menos pesquisas em discos). No entanto, se uma consulta utiliza LIMIT para recuperar somente parte dos registros, o MySQL irá utilizar um índice de qualquer forma, pois assim pode encontrar os poucos registros mais rapidamente e retornar o resultado.