MySQL Learning Summary (iii) index

Add some of the things that are constrained in the previous chapter. We recommend the "constraint abbreviation _ field name" When setting the name of the constraint, which makes the person look clear. For example: Fk_deptno, we can probably know by the name of the index that this is a foreign key set about the Deptno field.

An index contains one or more column fields in a table that are sorted in a certain order. The index is created primarily to increase the speed at which data is retrieved from the table. But there are pros and cons to creating indexes, and the advantage is that by indexing, you can improve the speed of queries. But too many indexes will take up a lot of disk space, so be sure to weigh the pros and cons when creating an index.

The following conditions are appropriate for creating an index:

(1) fields that are frequently queried

(2) fields that often appear in a group

(3) The primary key or the foreign key field of the table

(4) Setting a unique integrity constraint field

The following conditions are not appropriate for creating an index:

(1) fields that are rarely used in queries

(2) A field with many duplicate values

Index name when set, Suggest "index_ field name", this looks very clear.

Indexes can be divided into two categories, automatic indexing and manual indexing, depending on how they are created. By name we can simply understand that automatic indexing is an automatically created index (like crap). ), the MySQL system automatically creates indexes when setting the table's primary key or other set integrity constraints. The manual index refers to the index that we created on the table, as shown in:

1. Create an index

1.1. Create a normal index

CREATE TABLE table_name (

Property name Data type,

Property name Data type,

...

Index name (property name 1)

)

1.2. Create a normal index on a table that already exists

CREATE index index name on table name (property name)

1.3. ALTER table to create a normal index

ALTER TABLE table_name ADD index index name (property name)

2. Create a unique index

The unique index is that when the index is created, the limit index value must be a unique value, and the index of that type can query a record more quickly.

2.1. Create a unique index when creating a table

CREATE TABLE table_name (

Property name Data type,

Property name Data type,

...

UNIQUE Index name (property name 1)

)

2.2. Create a unique index on a table that already exists

CREATE UNIQUE index index name on table name (property name)

2.3. ALTER table creates a unique index

ALTER TABLE table_name ADD UNIQUE Index name (property name)

Note: When you create a unique index, the unique index creates a failure if the field in the table has a null value.

3. Create a full-text index

A full-text index is primarily associated with a field in a database that has a data type of char, VARCHAR, and text, so that you can query the fields of a large string type more quickly.

3.1. Create a full-text index when creating a table

CREATE TABLE table_name (

Property name Data type,

Property name Data type,

...

Fulltext Index name (property name 1)

)

3.2. Create a full-text index on a table that already exists

CREATE Fulltext index index name on table name (property name)

3.3. ALTER table to create a full-text index

ALTER TABLE table_name ADD fulltext index name (property name)

4. Create an index from SQLyog

To create an indexed table, select the Change Table menu and click "2 Indexes" as shown in:

With SQLyog software, we can create indexes more quickly and easily. And the interface is very simple, easy to get started.

5. Delete Index

SQL syntax: DROP INDEX index_name on table_name;

Through the SQLyog software, in the table-to-index, click the plus sign in front of the node, select the index to delete, right-select "Delete Index", follow the prompts to select Yes, delete successfully,

or delete it under the index column of the change table, as described above.

MySQL Learning Summary (iii) index