1. Meaning and characteristics of the index
Index: Created on a table, is a structure that sorts the values of one or more columns in a database table.
Storage type: B-Tree (BTREE) index and hash index; InnoDB and MyISAM Support BTREE index, memory support BTREE index and hash index
Pros and Cons: advantages--Improve the speed of retrieving data; Cons: Creating and maintaining indexes takes time
2. Index classification
1) Normal index: No restrictions
2) Uniqueness Index: Use unique parameter (primary key is a special uniqueness index)
3) Full-text indexing: Use the fulltext parameter to create only fields on char varcher or text type
4) Single-column index: Create an index on a single field in a table, a uniqueness index, or a full-text index
5) Multi-column index: Create an index on multiple fields of a table; a uniqueness index or full-text index
6) Spatial index: Using spatial parameters, only the MyISAM storage engine supports spatial indexes, must be built on spatial data types, and must be non-empty
3. How to design an index
4. Create an index
Three ways: Create an index when creating a table, create an index on a table that already exists, and use the ALTER TABLE statement to create
4.1 Creating an index when creating a table
1) Create a normal index
indexing the ID field of a table CREATE TABLE INT VARCHAR(a), sex BOOLEAN,INDEX(id));
2) Create a Uniqueness Index
CREATE TABLE INT UNIQUE VARCHAR(),UNIQUEINDEXASC);
3) Create a full-text index
CREATE TABLE INT VARCHAR(INDEX index3_info (info)) ENGINE=MyISAM;
4) Create a single-column index
CREATE TABLE INT VARCHAR(+),INDEX index4_st (subject (ten)));
5) Creating Multi-column indexes
CREATE TABLE INT VARCHAR(4CHAR),INDEX index5_ NS (name, sex));
6) Create a spatial index
CREATE TABLE INT , Space not NULL INDEX index6_sp (space)) ENGINE=MyISAM;
4.2 Creating an index on a table that already exists
4.3 Creating an index with an ALTER TABLE statement
5. Deleting an index
MySQL Getting started is simple: 5 index