Na verdade, o problema aqui é que isso parece um índice de prefixo. Não vejo a definição da tabela na pergunta, mas sub_part= 700? Você não indexou a coluna inteira, portanto, o índice não pode ser usado para classificação e também não é útil como índice de cobertura. Ele só poderia ser usado para encontrar as linhas que "podem" corresponder a WHEREe a camada do servidor (acima do mecanismo de armazenamento) teria que filtrar ainda mais as linhas correspondentes. Você realmente precisa de 1000 caracteres para um sobrenome?

atualização para ilustrar: Eu tenho uma tabela de teste de tabela com pouco mais de 500 linhas, cada uma com o nome de domínio de um site em uma coluna domain_name VARCHAR(254) NOT NULLe sem índices.

mysql> alter table keydemo add key(domain_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

Com a coluna completa indexada, a consulta usa o índice:

mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | keydemo | index | NULL          | domain_name | 764     | NULL |  541 | Using index |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.01 sec)

Então, agora, vou descartar esse índice e indexar apenas os primeiros 200 caracteres de domain_name.

mysql> alter table keydemo drop key domain_name;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table keydemo add key(domain_name(200));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | keydemo | ALL  | NULL          | NULL | NULL    | NULL |  541 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql>

Voilá.

Observe também que o índice, com 200 caracteres, é maior que o valor mais longo na coluna...

mysql> select max(length(domain_name)) from keydemo;
+--------------------------+
| max(length(domain_name)) |
+--------------------------+
|                       43 |
+--------------------------+
1 row in set (0.04 sec)

... mas isso não faz qualquer diferença. Um índice declarado com um comprimento de prefixo só pode ser usado para pesquisas, não para classificação e não como índice de cobertura, pois não contém o valor completo da coluna, por definição.

Além disso, as consultas acima foram executadas em uma tabela InnoDB, mas executá-las em uma tabela MyISAM produz resultados praticamente idênticos. A única diferença neste caso é que a contagem do InnoDB para rowsestá ligeiramente desligada (541), enquanto MyISAM mostra o número exato de linhas (563), o que é um comportamento normal, já que os dois mecanismos de armazenamento lidam com mergulhos de índice de maneira muito diferente.

Eu ainda afirmaria que a coluna last_name provavelmente é maior do que o necessário, mas ainda é possível indexar a coluna inteira, se você estiver usando o InnoDB e executando o MySQL 5.5 ou 5.6:

Por padrão, uma chave de índice para um índice de coluna única pode ter até 767 bytes. O mesmo limite de comprimento se aplica a qualquer prefixo de chave de índice. Consulte a Seção 13.1.13, “ CREATE INDEXSintaxe”. Por exemplo, você pode atingir esse limite com um índice de prefixo de coluna de mais de 255 caracteres em uma coluna TEXTou VARCHAR, assumindo um UTF-8conjunto de caracteres e o máximo de 3 bytes para cada caractere. Quando a innodb_large_prefixopção de configuração está habilitada, esse limite de comprimento é aumentado para 3072 bytes, para InnoDBtabelas que usam os formatos DYNAMICe linha.COMPRESSED

^{— http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html}

PROBLEMA #1

Veja a consulta

select last_name from employees order by last_name;

Não vejo uma cláusula WHERE significativa, nem o MySQL Query Optimizer. Não há incentivo para usar um índice.

PROBLEMA #2

Veja a consulta

select last_name from employees force index(idx_last_name) order by last_name; 

Você deu um índice, mas o Query Opitmizer assumiu. Eu já vi esse comportamento antes ( Como forço um JOIN para usar um índice específico no MySQL? )

Por que isso deveria acontecer?

Sem uma WHEREcláusula, o Query Optimizer diz o seguinte para si mesmo:

• Esta é uma tabela InnoDB
• É uma coluna indexada
• O índice tem o row_id do gen_clust_index (também conhecido como Índice Agrupado)
• Por que devo olhar para o índice quando
  • não há WHEREcláusula?
  • Eu sempre teria que voltar para a mesa?
• Como todas as linhas em uma tabela InnoDB residem nos mesmos blocos de 16K que o gen_clust_index, farei uma varredura completa da tabela.

O Query Optimizer escolheu o caminho de menor resistência.

Você vai ficar um pouco chocado, mas aqui vai: Você sabia que o Query Optimizer irá lidar com o MyISAM de forma bem diferente?

Você provavelmente está dizendo HUH ???? COMO AS ????

MyISAM armazena os dados em um .MYDarquivo e todos os índices no .MYIarquivo.

A mesma consulta produzirá um plano EXPLAIN diferente porque o índice reside em um arquivo diferente dos dados. Por quê ? Aqui está o porquê:

• Os dados necessários ( last_namecoluna) já estão ordenados no.MYI
• Na pior das hipóteses, você terá uma verificação completa do índice
• Você só acessará a coluna last_namedo índice
• Você não precisa peneirar indesejados
• Você não acionará a criação de arquivos temporários para classificação

Como pode ter tanta certeza disso? Eu testei esta teoria de trabalho sobre como usar um armazenamento diferente gerará um plano EXPLAIN diferente (às vezes um melhor): Um índice deve cobrir todas as colunas selecionadas para que ele seja usado para ORDER BY?

Eu fiz uma resposta sobre porque um comentário não suportará formatação e o RolandoMySQL DBA falou sobre gen_clust_index e innodb. E isso é muito importante em uma tabela baseada em innodb. Isso vai além do conhecimento normal do DBA porque você precisa ser capaz de analisar o código C.

Você deve SEMPRE SEMPRE fazer uma CHAVE PRIMÁRIA ou uma CHAVE ÚNICA se estiver usando o Innodb. Se você não fizer isso, o innodb usará seu próprio ROW_ID gerado, o que pode fazer mais mal do que bem.

Vou tentar explicar fácil porque a prova é baseada em código C.

/**********************************************************************//**
Returns a new row id.
@return the new id */
UNIV_INLINE
row_id_t
dict_sys_get_new_row_id(void)
/*=========================*/
{
    row_id_t    id;

    mutex_enter(&(dict_sys->mutex));

    id = dict_sys->row_id;

    if (0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN)) {
          dict_hdr_flush_row_id();
    }

    dict_sys->row_id++;
    mutex_exit(&(dict_sys->mutex));
    return(id);
}

Primeiro problema

mutex_enter(&(dict_sys->mutex));

Esta linha garante que apenas um thread possa acessar dict_sys->mutex ao mesmo tempo. E se o valor já tiver sido mutexado ... sim, um thread tem que esperar para que você obtenha algo como um bom recurso aleatório como bloqueio de thread ou se você tiver mais tabelas sem sua própria PRIMARY KEY ou UNIQUE KEY, então você teria um bom recurso com innodb ' bloqueio de tabela ' não é esta não a razão pela qual o MyISAM foi substituído pelo InnoDB por causa do bom recurso que é chamado de bloqueio baseado em registro/linha.

Segundo problema

(0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN))

os cálculos do módulo (%) são lentos não são bons se você estiver inserindo em lote porque precisa ser recalculado toda vez ... e porque DICT_HDR_ROW_ID_WRITE_MARGIN (valor 256) é uma potência de dois, isso pode ser feito muito mais rápido ..

(0 == (id & (DICT_HDR_ROW_ID_WRITE_MARGIN - 1)))

Nota lateral se o compilador C foi configurado para otimizar e é um bom otimizador, o otimizador C corrigirá o código "pesado" para a versão mais leve

lema da história sempre crie sua própria PRIMARY KEY ou certifique-se de ter um índice UNIQUE ao criar uma tabela desde o início