Blogs

MySQL – INDEXES – Tutorialspoint

Posted on by admin

A database index is a data structure that improves the speed of operations on a table. Indexes can be created using one or more columns, providing the basis for both quick random searches and efficient order of access to records.

When creating the index, you must consider that all columns will be used to perform SQL queries and create one or more indexes on those columns.

Practically, indexes are also a type of tables, which keep the primary key or index field and a pointer to each record in the actual table.

Users cannot see the indexes, they are only used to speed up queries and will be used by the database search engine to locate records very quickly.

INSERT and UPDATE statements take longer on tables that have indexes, while SELECT statements become fast on those tables. The reason is that when inserting or updating, a database also needs to insert or update index values.

Simple and

unique index

You can create a single index on a table. A single index means that no two rows can have the same index value. This is the syntax for creating an index on a table.

CREATE UNIQUE INDEX index_name IN table_name (COLUMN1, COLUMN2,…);

You can use one or more columns to create an index.

For example, we can create an index on tutorials_tbl using tutorial_author.

CREATE UNIQUE INDEX AUTHOR_INDEX IN tutorials_tbl (tutorial_author)

You can create a simple index on a table. Simply omit the UNIQUE keyword from the query to create a simple index. A simple index allows duplicate values in a table.

If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name.

MYSQL> CREATE tutorials_tbl SINGLE INDEX AUTHOR_INDEX (tutorial_author DESC) ALTER command to add and

delete INDEX

There are four types of statements for adding indexes to a table

− ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

  • − This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement creates an index for which values must be unique (except for null values, which can appear multiple times).

  • ALTER TABLE tbl_name ADD index_name INDEX (column_list): Adds an ordinary index in which any value can appear more than once.

  • ALTER TABLE tbl_name ADD FULL-TEXT index_name (column_list): This creates a special full-text index that is used for full-text search purposes.

The following block of code is an example for adding index to an existing table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can delete any INDEX using the DROP clause together with the ALTER command.

Try the following example to delete the index you created earlier.

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

You can delete any INDEX using the DROP clause together with the ALTER command.

ALTER Command to add and drop the PRIMARY KEY

You can also add a primary key in the same way. But make sure that the primary key works in columns, which are NOT NULL.

The following block of code is an example for adding the primary key to an existing table. This will cause a NON-NULL column first and then add it as a primary key.

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

You can use the ALTER command to delete a primary key as follows:

mysql> ALTER TABLE, testalter_tbl DROP PRIMARY KEY;

To remove an index that is not a PRIMARY KEY, you must specify the name of the index.

Viewing index information

You can use the SHOW INDEX command to list all indexes associated with a table. Vertical format output (specified by \G) is often useful with this statement, to avoid an enveloping long line −

Try the following example

: mysql> SHOW table_name\G INDEX……..

admin