The best MySQL Index Tutorial In 2024, In this tutorial you can learn General index,Unique Index,Use ALTER command to add and delete indexes,Use ALTER command to add and delete the primary key,Display index information,
MySQL MySQL build indexes for efficient operation is very important, indexes can greatly improve the retrieval speed of MySQL.
For example, if a reasonable design and use the index MySQL is a Lamborghini, then there is no design and use of the index MySQL is a tricycle.
Index points separate index and combination index. Separate index that contains only a single column index, a table can have multiple separate index, but this is not a composite index. Composite index, namely a cable comprising a plurality of columns.
When you create an index, you need to ensure that the index is used in an SQL query conditions (generally as a condition of the WHERE clause).
In fact, the index is also a table that holds the primary key and index fields, and points to record the entity table.
The above are talking about the benefits of using the index, but the excessive use of the index will result in abuse. Therefore, the index also has its disadvantages: While the index greatly increased query speed, while it will reduce the speed of updating the table, such as table INSERT, UPDATE, and DELETE. Because the update table, MySQL is not only to save the data, but also save about index file.
Indexing takes up disk space in the index file.
This is the basic index, it does not have any restrictions. It has created the following ways:
CREATE INDEX indexName ON mytable(username(length));
If it is CHAR, VARCHAR type, length can be less than the actual length of the field; if BLOB and TEXT types, you must specify the length.
ALTER mytable ADD INDEX [indexName] ON (username(length))
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
DROP INDEX [indexName] ON mytable;
It is similar to the previous general index, it is different: the value of the index columns must be unique, but allow free value. If it is a combination of the index, the column value must be unique. It has created the following ways:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
There are four ways to add index data table:
The following examples add an index in the table.
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
You can also use the DROP clause to remove the index in the ALTER command. Try the following examples to delete the index:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
Only act on a primary key column, when you add a primary key index, you need to make sure that the default primary key is not empty (NOT NULL). Examples are as follows:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
You can also use the ALTER command to delete the primary key:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
Just specify PRIMARY KEY Delete the specified time, but when you delete the index, you must know the name of the index.
You can use the SHOW INDEX command to list the table index information. By adding \ G to format the output.
Try the following examples:
mysql> SHOW INDEX FROM table_name\G /en/en/en/en