A brief analysis of mysql index

A database index is a data structure whose purpose is to increase the speed of table operations. Indexes can be created using one or more columns that provide fast random lookups and efficient sorting of access to records.

To create an index, you should think about which columns will be used to make SQL queries and create one or more indexes on those columns.

In fact, an index is also a table that holds a pointer to the primary key or index field and points each record to the actual table type.

Indexes are not visible to users, they are only used to speed up queries and will be used by the database search engine to increase the speed when finding records.

INSERT and UPDATE statements take more time to create indexes, as SELECT statements operate quickly on these tables. The reason is that when inserting or updating data, the database needs to also update the inserted or updated index value.

Simple and unique indexes

You can create unique indexes on a table. A unique index means that two rows cannot have the same index value. Following is the syntax to create an index on a table:

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

An index can be created using one or more columns. For example, we can create an index on tutorials_tbl using tutorial_author.

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

You can create a simple index on a table. To create a simple index simply omit the UNIQUE keyword. Simple indexes can contain repeated values ​​in the table.

If you want the values ​​of the indexed column to be sorted in descending order, you can add the reserved word DESC after the column name.

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)

Use the ALTER command to add and drop indexes

There are four types of indexes that can be added to a table:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): Adds a primary key (PRIMARY KEY), which means that the index value must be unique and cannot be empty.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): creates an index whose values ​​must be unique (except that NULL values ​​cannot be used, while others can appear multiple times).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): adds a normal index where any value occurs multiple times.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): creates a special FULLTEXT index for text searching purposes.

Following is an example of adding an index to an existing table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

An index can be dropped by using the ALTER command with the DROP clause. Try the following example to delete the index created above.

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

Use the ALTER command to add and delete a PRIMARY KEY

You can also add a primary key in the same way. However, to ensure that the primary key is used properly in the column, you need to specify the use of NOT NULL.

Following is an example of adding primary key in an existing table. The column needs to have the NOT NULL attribute added before adding 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 delete a primary key using the ALTER command as follows:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

To delete an index that is not a primary key, you must specify the index name.

Display index information

You can use the SHOW INDEX command to list all indexes associated with a table. Format output vertically (specified by \G). This is often useful to avoid long lines summarizing the output:

Try the following example:

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

Summarize

The above is the MySQL index introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!