Easy to get started with MySQL-Study Notes-Chapter 2 Index

Document directory

• 7.1.1. Meanings and features of Indexes
• 7.1.2. Index classification
• 7.1.3 index design principles
• 7.2.1 create an index when creating a table
• 7.2.2 create an index on an existing table
• 7.2.3 use the alter table statement to create an index

Chapter 2 Index

In MySQL, all data types can be indexed, including common indexes, unique indexes, full-text indexes, single-column indexes, multi-column indexes, and spatial indexes.

7.1. Description of indexes 7.1.1. Meanings and features of Indexes

B-tree index and hash Index

Advantage: Improves the query, joint query, grading, and sorting time

Disadvantage: it takes time to maintain (create, update, and maintain) indexes.

 

7.1.2. Index classification

1. Common Index

Without any restrictions

2. Unique Index

Use unique Parameters

3. Full-text index

The Fulltext parameter can be used only for char, varchar, and text fields. Only the MyISAM storage engine supports full-text indexing.

4. Single Column Index

A common index, unique index, or full-text index created on a field

5. Multi-column Index

A common index, unique index, or full-text index created on multiple fields

6. spatial indexes

With the spatial parameter, only the MyISAM storage engine supports spatial indexes. It must be based on spatial data types and must be non-empty. It is rarely used by beginners.

 

7.1.3 index design principles

1. Select a unique index

2. Create an index for fields that frequently require sorting, grouping, and Union operations

Such as order by, group by, distinct, union, and other operation fields, especially sorting

3. Create an index for fields that are often used as query Conditions

4. Limit the number of Indexes

Avoid wasting too much space

5. Try to use indexes with a small amount of data

6. Try to use the prefix for indexing.

For example, index text fields with the first n characters

7. Delete indexes that are no longer in use or rarely used

 

7.2 create an index

Three methods:

1. Create an index when creating a table

2. Create an index for an existing table

3. Use the alter table statement to create an index

 

7.2.1 create an index when creating a table

Create Table Name (attribute name data type [complete constraints],

Attribute name data type [complete constraints],

...

[Unique | Fulltext | spatialindex | key [alias] (attribute name 1 [(length)] [ASC | DESC])

);

 

1. Create a Common Index

Create tableindex1 (ID int,

Name varchar (20 ),

Sex Boolean,

Index (ID)

);

Show createtable index1 \ G;

 

2. Create a unique index

Create tableindex2 (ID int unique,

Name varchar (20 ),

Unique index index2_id (id asc)

);

Show createtable index2 \ G;

We can see that two unique index IDs and index2_id are created on the field ID. Of course, this is unnecessary.

 

3. Create a full-text index

Create tableindex3 (ID int,

Info varchar (20 ),

Fulltext index index3_info (Info)

) Engine = MyISAM;

 

4. Create a single column Index

Create tableindex4 (ID int,

Subject varchar (30 ),

Indexindex4_st (subject (10 ))

);

Note: Only index the first 10 characters of Subject

 

5. Create multi-column Indexes

Create tableindex5 (ID int,

Name varchar (20 ),

Sex char (4 ),

Index index5_ns (name, sex)

);

 

Explain select * From index5 where name = '000000' \ G;

Explain select * From index5 where name = '000000' and sex = 'n' \ G;

6. Create a spatial index

Create tableindex6 (ID int,

Space geometry not null,

Spatial Index index6_sp (Space)

) Engine = MyISAM;

 

7.2.2 create an index on an existing table

Create [unique | Fulltext | spatial] Index name on table name (attribute name [(length)] [ASC | DESC]);

 

1. Create a Common Index

Create indexindex7_id on example0 (ID );

 

2. Create a unique index

Create uniqueindex index_8_id on index8 (course_id );

 

3. Create a full-text index

Create fulltextindex index9_info on index9 (Info );

 

4. Create a single column Index

Create indexindex10_addr on index10 (address (4 ));

 

5. Create multi-column Indexes

Create indexindex11_na on index11 (name, address );

 

6. Create a spatial index

Create spatialindex index12_line on index12 (line );

 

7.2.3 use the alter table statement to create an index

Alter table table name Add [unique | Fulltext | spatial] Index name (attribute name [(length)] [ASC | DESC]);

 

1. Create a Common Index

Alter tableexample0 add index index12_name (name (20 ));

 

2. Create a unique index

Alter tableindex14 add unique index index14_id (course_id );

 

3. Create a full-text index

Alter tableindex15 add index index15_info (Info );

 

4. Create a single column Index

Alter tableindex 16 add index index16_addr (address (4 ));

 

5. Create multi-column Indexes

Alter tableindex17 add index index17_na (name, address );

 

6. Create a spatial index

Alter tableindex18 add index index18_line (line );

 

 

7.3 delete an index

Drop Index name on table name;

 

Drop index ID onindex1;