MySQL – Indexes
In the database, an index is a data structure that improves the speed of operations within a table. It is possible to create an index based on one or more columns, which provides the basis for both rapid random lookups and efficient ordering of records.
It should be considered when creating an index which columns will be used to make SQL queries and whether one or more indexes should be created on those columns.
Generally, indexes are also types of tables, which contain the primary key or index field and a pointer to each record.
There is no way for users to see the indexes, they are simply used to speed up queries. The Database Search Engine will use the indexes to locate records in a very short period of time.
When tables have indexes, INSERT and UPDATE statements take longer, whereas SELECT statements become faster. As a result, when inserting or updating data, a database must also insert or update index values.
Simple and Unique Index
A unique index can be created on a table. Having a unique index means that no two rows can have the same index value. The following syntax is used to create an index on a table.
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);
Indexes can be created using one or more columns.
For example, we can create an index on trainer_tbl using trainer_deatils.
CREATE UNIQUE INDEX AUTHOR_INDEX ON trainer_tbl (trainer_deatils)
A simple index can be created on a table. To create a simple index, simply omit the UNIQUE keyword from the query. A simple index allows duplicate values to be stored in a table.
If If you wish to index the values in a column in descending order, add the reserved word DESC after the column name.
CREATE UNIQUE INDEX AUTHOR_INDEX ON trainer_tbl (trainer_deatils DESC)
ALTER command to add and drop INDEX
For adding indexes to a table, there are four types of statements.
- This statement adds a PRIMARY KEY to the table tbl_name, which means that the index values must be unique and cannot be NULL.
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement creates an index in which the values must be unique (except for NULL values, which may appear multiple times).
- ALTER TABLE tbl_name ADD INDEX index_name (column_list) − This creates an ordinary index in which any value may appear more than once.
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) Using this option, a special FULLTEXT index will be created for text searches.
This code block illustrates how to add an index to an existing table.
ALTER TABLE testalter_tbl ADD INDEX (c);
With the ALTER command, you can drop any INDEX by using the DROP clause.
You can drop the index created above by following the example below.
ALTER TABLE testalter_tbl DROP INDEX (c);
With the ALTER command, you can drop any INDEX by using the DROP clause.
ALTER Command to add and drop the PRIMARY KEY
In the same manner, you can also add a primary key. However, make sure the Primary Key is applied to columns that are not null.
This code block illustrates how to add a primary key to an existing table. As a result of this, a column will be made not NULL first, and then it will be added as a primary key.
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
To drop a primary key, use the ALTER command as follows:
ALTER TABLE testalter_tbl DROP PRIMARY KEY
It is necessary to specify the index name when dropping an index that is not a PRIMARY KEY.
Displaying INDEX Information
In order to view all the indexes associated with a table, you can use the SHOW INDEX command. A vertical format (specified by /G) is often useful with this statement in order to avoid long line wraparounds
Take a look at the following example
SHOW INDEX FROM table_name\G
……..