Estou fazendo o salto do SQL Server para o MySQL e estou tentando entender qual seria a diferença entre criar um índice como parte da instrução create da tabela DDL versus criar uma instrução DDL separada para cada índice que você deseja criar.
A documentação do MySQL sobre criar estados de índice
Normalmente, você cria todos os índices em uma tabela no momento em que a própria tabela é criada com CREATE TABLE. Consulte a Seção 13.1.18, “Sintaxe de CRIAR TABELA”. Essa diretriz é especialmente importante para tabelas InnoDB, onde a chave primária determina o layout físico das linhas no arquivo de dados. CREATE INDEX permite adicionar índices a tabelas existentes.
Por que é importante para a tabela InnoDB ter o índice criado como parte do DDL da tabela de criação? ( Atualize com mais detalhes abaixo )
Quais são os possíveis problemas com a criação de um índice após a criação da tabela?
Atualizar
Minha pergunta foi em relação à declaração no site do MySQL sobre como criar o índice durante a criação da tabela versus criá-lo após criar a tabela, mas antes de preenchê-la com dados. A declaração no site do MySQL faz parecer que há um benefício, desempenho ou não, ao usar tabelas InnoDB que declarar os índices dentro da declaração DDL para a tabela é a melhor prática, mas eles não explicam o porquê.
Referenciando como eu abordaria isso no SQL Server, em parte por causa das limitações de DDL e em parte porque gosto da natureza explícita, é que eu faria minha instrução DDL e atribuiria a chave primária junto com quaisquer restrições de chave estrangeira. Depois disso, eu atribuiria meu índice clusterizado, se não fosse a chave primária, e índices secundários (únicos e não exclusivos). Mas em qualquer abordagem se eu fosse criar meu índice clusterizado com a tabela DDL ou separar o resultado final seria o mesmo. Sua declaração está fazendo parecer que o RDMS lidará com a criação da tabela de maneira diferente, dependendo da abordagem, mesmo que o resultado final seja o mesmo, uma chave primária e um índice clusterizado.
No InnoDB, o
PRIMARY KEYé, por definição, agrupado e organizado por BTree. Se você preencher uma tabela antes de especificar o PK desejado, um PK oculto será gerado. Este PK oculto deve ser removido e substituído pelo PK desejado e a tabela deve ser reordenada de acordo com o novo PK. Nenhum "dano" é feito, mas é necessário um tempo extra e o BTree pode se tornar mais fragmentado.Adicionar chaves secundárias (qualquer chave que não seja o PK) separadamente do
CREATE TABLEpode ou não ser ineficiente. Mas, novamente, nenhum "dano" é feito. A ineficiência depende da versão, da natureza do índice, etc.FOREIGN KEYssão outra questão -- eles devem ser aplicados na "ordem certa". Isso pode ser conseguidoCREATE TABLEscolocando o na "ordem certa" ou adicionando separadamente os FKs na "ordem certa".OK, a documentação nesta área é fraca. Você pode denunciá-lo em bugs.mysql.com.
Prefiro ter (e ver) tudo dentro do
CREATE TABLEDDL.Se você colocar uma chave primária em uma tabela InnoDB, ela agirá como uma tabela organizada por índice no SQL Server. Em outras palavras, o índice e os dados reais são armazenados juntos e geralmente na ordem do índice. Seria como criar uma lista telefônica em ordem alfabética por sobrenome e depois decidir que queria "indexar" pelo primeiro nome - você teria que recorrer a tudo.
Isso tem muito mais detalhes:
http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/