General index

• Key at creation: INDEX
• KEY when viewing: KEY

#Amendment:
ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );
#In the book table, create a common index on the year [publication] field. The SQL statement is as follows:
CREATE TABLE book
(
bookid            	INT NOT NULL,
bookname          	VARCHAR(255) NOT NULL,
authors            	VARCHAR(255) NOT NULL,
info               	VARCHAR(255) NULL,
comment           	VARCHAR(255) NULL,
year_publication   	YEAR NOT NULL,
INDEX(year_publication)
);
#Table structure:
 SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
CREATE Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#The index name year'publication is automatically added to MySQL.
#Use the EXPLAIN statement to see if the index is in use:
explain select * from book where year_publication=1990 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
        Extra:
1 row in set (0.05 sec)

unique index

• Key at creation: UNIQUE INDEX
• Keyword when viewing: UNIQUE KEY

#Amendment:
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );
#Create a table t1 and use the UNIQUE keyword to create a UNIQUE index on the id field in the table.
CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
#Table structure:
 SHOW CREATE table t1 \G
*************************** 1. row ***************************
       Table: t1
CREATE Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
#A unique index named UniqIdx has been successfully established on the id field.

single column

• Keywords when creating: INDEX SingleIdx(name(20))
• Keywords in view: KEY SingleIdx (name(20))

#modify
ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );
#Create a table t2, and create a single column index on the name field in the table:
CREATE TABLE t2
(
id   INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);
#View table structure:
 SHOW CREATE table t2 \G
*************************** 1. row ***************************
Table: t2
CREATE Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#A single column index named SingleIdx with an index length of 20 is established.

Composite index

• Keywords when creating: INDEX MultiIdx(id, name, age(100))
• Keywords in view: KEY MultiIdx (id,name,age)

#modify
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );
#Create table t3, and establish a composite index on the id, name and age fields in the table. The SQL statement is as follows:
CREATE TABLE t3
(
id    INT NOT NULL,
name CHAR(30) 　NOT NULL,
age  INT NOT　 NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, age(100))
);
#View table structure:
 SHOW CREATE table t3 \G
*************************** 1. row ***************************
       Table: t3
CREATE Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#A composite index named MultiIdx is established.
#Leftmost prefix: uses the leftmost column set in the index to match rows. Such a column set is called the leftmost prefix.
#For example, here is an index composed of three fields: id, name and age. The index row is stored in the order of id/name/age. The index can search the following field combinations: (id, name, age), (id, name) or id.
#If the column does not form the prefix of the leftmost side of the index, MySQL cannot use the local index, such as (age) or (name,age) combination, index query cannot be used.
#In the t3 table, query the id and name fields, and use the EXPLAIN statement to view the index usage:
 explain select * from t3 where id=1 AND name='joe' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 94
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)
#When querying the id and name fields, the index of the name MultiIdx is used. If the name and age fields are combined or queried separately, the results are as follows:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
#The value of possible_keys and key is NULL, and the index created in t3 table is not used for query.

Full-text index

• Keywords at creation: FULLTEXT INDEX FullTxtIdx(info)
• Keywords in view: KEY MultiIdx (id,name,age)

#modify
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );
#FULLTEXT full-TEXT index can be used for full-TEXT search. FULLTEXT indexes are only supported by the MyISAM storage engine and are only CHAR, VARCHAR, and TEXT columns. Index is always on the whole column, and local (prefix) index is not supported.
[example9.5]Create table t4，In the table info Full text indexing on fields,SQLThe statement is as follows:
CREATE TABLE t4
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
age  INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;
#The storage engine is MyISAM, otherwise there will be an error in index creation.
#View table structure:
SHOW CREATE table t4 \G
*************************** 1. row ***************************
      Table: t4
CREATE Table: CREATE TABLE `t4` (
 `id` int(11) NOT NULL,
 `name` char(30) NOT NULL,
 `age` int(11) NOT NULL,
 `info` varchar(255) DEFAULT NULL,
 FULLTEXT KEY `FullTxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
#FULLTEXT index of FullTxtIdx.
#Full text index is very suitable for large datasets

Spatial index

• Keywords at creation: (g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g))
• Keywords when viewing: SPATIAL KEY spatIdx (g)

#modify
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);
#The spatial index must be created in a table of type MyISAM and the field of the spatial type must be non empty.
#Create table t5, and create a spatial index on the field whose spatial type is GEOMETRY. The SQL statement is as follows:
CREATE TABLE t5
( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;
#Structure:
CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;