CREATE INDEX - Statement to Create Indexes

MySQL Tutorials - Herong's Tutorial Examples

∟CREATE INDEX - Statement to Create Indexes

A tutorial example is provided on how to create indexes as part of CREATE TABLE statements or using CREATE INDEX standalone statements.

There are two ways to create indexes on a table:

1. Creating indexes using the CREATE TABLE statement:

CREATE TABLE tbl_name (col_name col_type col_options, ...,
INDEX idx_name (idx_column, ...),...);

2. Creating indexes using the CREATE INDEX statement:

CREATE INDEX idx_name ON tbl_name (idx_column,...);

To show the indexes of an existing table, you can use the show index statement:

SHOW INDEX FROM tbl_name;

Here is an example code on creating indexes:

-- CreateIndex.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
CREATE TABLE IF NOT EXISTS User (ID INT PRIMARY KEY AUTO_INCREMENT,
   Login CHAR(8) NOT NULL, Password CHAR(8) UNIQUE,
   Email CHAR(32) DEFAULT 'info@hy.com',
   INDEX LoginIndex (Login));
INSERT INTO User VALUES (null,'herong','8IS3KOXW','herong@hy.com');
INSERT INTO User (Login, Password, Email)
   VALUES ('mike','PZ0JG',null);
INSERT INTO User (Login, Password)
   VALUES ('mike','GZDN');
SELECT 'First show index';
SHOW INDEX FROM User;
CREATE INDEX EmailIndex ON User (Email);
SELECT 'Second show index';
SHOW INDEX FROM User;
DROP TABLE User;

Output:

First show index
First show index
Table Non_unique Key_name   Seq_in_index Column_name ...
User  0          PRIMARY    1            ID
User  0          Password   1            Password
User  1          LoginIndex 1            Login

Second show index
Second show index
Table Non_unique Key_name   Seq_in_index Column_name ...
User  0          PRIMARY    1            ID
User  0          Password   1            Password
User  1          LoginIndex 1            Login
User  1          EmailIndex 1            Email

Note that:

An index is automatically created for the primary key column.
An index is automatically created for each column that is defined as unique.

Also note that if a table is using MyISAM engine, its index block size is limited to 1000 bytes, which is less than 256 4-byte characters. If you are creating an index on long columns, you can reduce index length with the length option as shown below:

CREATE INDEX ComboIndex ON User (Name, Address);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

CREATE INDEX ComboIndex ON User (Name(80), Address(160));

The last statement in the above example creates a composite index of 240 characters (960 bytes) by taking the first 80 characters from "Name" and the first 160 characters from "Address".