The "MySQL" MySQL index

An index is a database object that resides in a pattern, although the index is always subordinate to the data table, but it is also part of the database object as a data table. The only effect of creating an index is to speed up queries to the table, which reduces the disk I/O by using fast path access methods to quickly locate data.

MySQL uses the statistics in the INFORMATION_SCHEMA database to hold all the index information in the DB instance, and the user can query the table to get the index information for that database.

There are two ways of creating an index:

1. Automatic: When defining primary key constraints, foreign key constraints, and unique key constraints on a table, the system automatically creates an index of that data column.

2. Manual: User can create INDEX ... Statement to create an index.

The index acts like a book's directory, with almost no book without a table of contents, and so few tables have no index. There can be more than one indexed column in a table, and each index column is used to speed up the query for that column.

Syntax for creating indexes:

Create Index  on table_name (column[, Column]...);

The following index will increase the query on the employee table based on the Last_Name field

Create Index  on employee (last_name);

You can also index multiple columns at the same time.

Create Index  on employee (first_name,last_name);

MySQL Delete index requires a table to be specified:

Drop Index  on table name

The following SQL statement deletes the EMP_LAST_NAME_IDX2 index on the employee table

Drop Index  on employee

Some databases do not need to specify table names when they delete indexes because they require that each index have its own name when indexing is required, so you do not need to specify a table name, which is used by columns such as Oracle. However, MySQL must specify the table name when deleting an index, as long as the index within the same table cannot have the same name.

Indexes have advantages, and of course there are disadvantages to index:

1. Similar to the book's Directory, when records in a data table are added, deleted, modified, the database needs to maintain the index, so there is some overhead.

2. Storing the index information requires a certain amount of disk space.

The "MySQL" MySQL index