MySQL index create delete add modify command

Simple Unique index:

A table that can create a unique index. A unique index means that two rows cannot have the same index value. Here is the syntax to create the index table

The code is as follows	Copy Code
CREATE UNIQUE INDEX index_name On table_name (column1, Column2,...);

You can use one or more columns to create an index. For example, we can create an index using the Tutorial_author column on the table tutorials_tbl

The code is as follows	Copy Code
CREATE UNIQUE INDEX Author_index On Tutorials_tbl (Tutorial_author)

You can create a table with a simple index. Create a simple index as long as you omit a query that omits the unique keyword. A simple index allows a value in a repeating table.

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

The code is as follows	Copy Code
mysql> CREATE UNIQUE INDEX author_index On Tutorials_tbl (Tutorial_author DESC)

Alter command to add and delete indexes:

There are four types of statement tables that add indexes:

The code is as follows	Copy Code
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

This statement adds a primary key, which means that the index value must be unique and cannot be null.

The code is as follows	Copy Code
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

This statement creates an index whose value must be unique (except for a null value, which may occur multiple times).

The code is as follows	Copy Code
ALTER TABLE tbl_name ADD INDEX index_name (column_list):

This adds an ordinary index in which any value may appear more than once.

The code is as follows	Copy Code
ALTER TABLE tbl_name ADD fulltext index_name (column_list):

This creates a special Fulltext index for the purpose of text search.

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

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can delete any index by using the DROP clause and using the ALTER command. Try the following example to create an index drop above.

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

You can delete any index by using the DROP clause T and using the ALTER command. Try the following example of the index x created above.

Alter command to add and remove primary KEY:

You can add a primary key, as well as in the same way. But make sure that the primary key is not NULL on the column.

This is an example of adding a primary key to an existing table. This will not be a null column and then add it as a primary key.

The code is as follows	Copy Code
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 remove the primary key as follows:

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

To delete an index, this is not a primary KEY, so you must specify the name of the index.

Display index Information:
You can use the show index command to list all the associated index tables. The vertical format output (specified by G) is often useful in this sentence to avoid long time line overlap:

Try the following example:

The code is as follows	Copy Code
Mysql> Show INDEX from Table_nameg

........

Add index
Mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);
Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee ADD index emp_name (name);

Index of the Primary keyword

Mysql> ALTER TABLE name add primary key (field name);
Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee ADD primary key (ID);

Index with unique restriction criteria
Mysql> ALTER TABLE name add
Unique index name (field name);
Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee add unique emp_name2 (cardnumber);

View the index of a table

Mysql> Show index from table name;
Example:

The code is as follows	Copy Code
Mysql> Show index from employee;

Delete an index
mysql> ALTER TABLE name DROP INDEX index name;
Example:

The code is as follows	Copy Code
Mysql>alter table employee DROP index emp_name;

Summary indexing and optimization

1. Select the data type of the index

MySQL supports many data types, and choosing the right data type to store data has a significant impact on performance. Generally, you can follow some of the following guidelines:

(1) Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caching, and are faster to handle.
(2) A simple data type is better: integer data has less processing overhead than characters because the strings are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.
(3) Try to avoid null: you should specify NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they make indexing, indexing, and comparison operations more complex. You should use 0, a special value, or an empty string instead of a null value.