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:

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".

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

Using DDL to Create Tables and Indexes

 CREATE TABLE - Statement to Create Tables

 Column Options When Creating Tables

CREATE INDEX - Statement to Create Indexes

 ALTER TABLE - Statement to Alter Table Structures

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB