MySQLi - INDEXES

Um índice de banco de dados é uma estrutura de dados que melhora a velocidade das operações em uma tabela. Os índices podem ser criados usando uma ou mais colunas, fornecendo a base para pesquisas aleatórias rápidas e ordenação eficiente de acesso aos registros.

Ao criar o índice, deve-se considerar quais são as colunas que serão utilizadas para fazer consultas SQL e criar um ou mais índices nessas colunas.

Praticamente, os índices também são tipos de tabelas, que mantêm a chave primária ou campo de índice e um ponteiro para cada registro na tabela real.

Os usuários não podem ver os índices, eles são usados ​​apenas para agilizar as consultas e serão usados ​​pelo Database Search Engine para localizar registros muito rapidamente.

As instruções INSERT e UPDATE levam mais tempo em tabelas com índices onde, conforme as instruções SELECT, se tornam mais rápidas nessas tabelas. O motivo é que, ao fazer a inserção ou atualização, o banco de dados também precisa inserir ou atualizar os valores do índice.

Índice Simples e Único

Você pode criar um índice exclusivo em uma tabela. Um índice exclusivo significa que duas linhas não podem ter o mesmo valor de índice. Aqui está a sintaxe para criar um índice em uma tabela.

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

Você pode usar uma ou mais colunas para criar um índice. Por exemplo, podemos criar um índice em tutorials_inf usando NAME_INDEX.

CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf(name);

Você pode criar um índice simples em uma tabela. Apenas omita a palavra-chave UNIQUE da consulta para criar um índice simples. O índice simples permite valores duplicados em uma tabela.

Se você deseja indexar os valores em uma coluna em ordem decrescente, pode adicionar a palavra reservada DESC após o nome da coluna.

mysql> CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf (name DESC);

Comando ALTER para adicionar e eliminar INDEX

Existem quatro tipos de instruções para adicionar índices a uma tabela -

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) - Esta instrução adiciona uma PRIMARY KEY, o que significa que os valores indexados devem ser exclusivos e não podem ser NULL.

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) - Esta instrução cria um índice para o qual os valores devem ser exclusivos (com exceção dos valores NULL, que podem aparecer várias vezes).

  • ALTER TABLE tbl_name ADD INDEX index_name (column_list) - Isso adiciona um índice comum no qual qualquer valor pode aparecer mais de uma vez.

  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) - Isso cria um índice FULLTEXT especial que é usado para fins de pesquisa de texto.

Aqui está o exemplo para adicionar índice em uma tabela existente.

mysql> ALTER TABLE tutorials_inf ADD INDEX (id);

Você pode eliminar qualquer INDEX usando a cláusula DROP junto com o comando ALTER. Experimente o seguinte exemplo para eliminar o índice criado acima.

mysql> ALTER TABLE tutorials_inf DROP INDEX (c);

Você pode eliminar qualquer INDEX usando a cláusula DROP junto com o comando ALTER. Experimente o seguinte exemplo para eliminar o índice criado acima.

Comando ALTER para adicionar e soltar PRIMARY KEY

Você também pode adicionar a chave primária da mesma maneira. Mas certifique-se de que a chave primária funcione em colunas, que NÃO são NULL.

Aqui está o exemplo para adicionar uma chave primária em uma tabela existente. Isso tornará uma coluna NOT NULL primeiro e depois a adicionará como uma chave primária.

mysql>  ALTER TABLE tutorials_inf MODIFY id INT NOT NULL;
mysql> ALTER TABLE tutorials_inf ADD PRIMARY KEY (id);

Você pode usar o comando ALTER para descartar uma chave primária da seguinte maneira:

mysql> ALTER TABLE tutorials_inf DROP PRIMARY KEY;

Para eliminar um índice que não seja uma PRIMARY KEY, você deve especificar o nome do índice.

Exibindo informações INDEX

Você pode usar o comando SHOW INDEX para listar todos os índices associados a uma tabela. A saída de formato vertical (especificada por \ G) geralmente é útil com esta instrução, para evitar o contorno de linhas longas -

Experimente o seguinte exemplo

mysql> SHOW INDEX FROM table_name\G
........