General index
- Key at creation: INDEX
- KEY when viewing: KEY
ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );
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)
);
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
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
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
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)
single column
- Keywords when creating: INDEX SingleIdx(name(20))
- Keywords in view: KEY SingleIdx (name(20))
ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);
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
Composite index
- Keywords when creating: INDEX MultiIdx(id, name, age(100))
- Keywords in view: KEY MultiIdx (id,name,age)
ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );
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))
);
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
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)
*************************** 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
Full-text index
- Keywords at creation: FULLTEXT INDEX FullTxtIdx(info)
- Keywords in view: KEY MultiIdx (id,name,age)
ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );
[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;
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
Spatial index
- Keywords at creation: (g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g))
- Keywords when viewing: SPATIAL KEY spatIdx (g)
ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);
CREATE TABLE t5
( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;
CREATE TABLE `t5` (
`g` geometry NOT NULL,
SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;