MySQL - Create Index

Table of content

A database index improves the speed of operations in a database table. They can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

Practically, indexes are a special type of lookup tables, that hold a pointer to each record into the actual table.

We can create indexes on a MySQL table in two scenarios: while creating a new table and on an existing table.

Creating Indexes on New Table

If we want to define an index on a new table, we use the CREATE TABLE statement.

Syntax

Following is the syntax to create an index on a new table −

CREATE TABLE(
 column1 datatype PRIMARY KEY,
 column2 datatype,
 column3 datatype,
 ...
 INDEX(column_name)
);

Example

In this example, we are create a new table CUSTOMERS and adding an index to one of its columns using the following CREATE TABLE query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   INDEX(ID)
);

To verify whether the index has been defined or not, we check the table definition using the following DESC statement.

DESC CUSTOMERS;

Output

The table structure displayed will contain a MUL index on the ID column as shown −

Field Type Null Key Default Extra
ID int NO MUL NULL
NAME varchar(20) NO NULL
AGE int NO NULL
ADDRESS char(25) YES NULL
SALARY decimal(18, 2) YES NULL

Creating Indexes on Existing Table

To create an index on existing table, we use the following SQL statements −

  • With CREATE INDEX Statement
  • With ALTER Command

CREATE INDEX Statement

The basic syntax of the CREATE INDEX statement is as follows −

CREATE INDEX index_name ON table_name;

In the following example, let us create an index on CUSTOMERS table. We are using CREATE INDEX statement here −

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

To check if the index is created on the table or not, let us display the table structure using DESC statement as shown below −

DESC CUSTOMERS;

Output

As we can see in the table below, a composite index is created on the 'NAME' column of CUSTOMERS table.

Field Type Null Key Default Extra
ID int NO MUL NULL
NAME varchar(20) NO MUL NULL
AGE int NO NULL
ADDRESS char(25) YES NULL
SALARY decimal(18, 2) YES NULL

ALTER... ADD Command

Following is the basic syntax of ALTER statement −

ALTER TABLE tbl_name ADD INDEX index_name (column_list);

Let us use ALTER TABLE... ADD INDEX statement in the following example to add an index to the CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD INDEX AGE_INDEX (AGE);

Output

As we can see in the table below, another composite index is created on the 'AGE' column of CUSTOMERS table.

Field Type Null Key Default Extra
ID int NO MUL NULL
NAME varchar(20) NO MUL NULL
AGE int NO MUL NULL
ADDRESS char(25) YES NULL
SALARY decimal(18, 2) YES NULL

Simple and Unique Index

A unique index is the one which cannot be created on two rows at once. Following is the syntax to create a unique index −

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

Example

Following example creates a unique index on the table temp −

CREATE UNIQUE INDEX UNIQUE_INDEX ON CUSTOMERS (Name);

Composite Indexes

We can also create an index on more than one column and it is called a composite index the basic syntax to create a composite index is as follows −

CREATE INDEX index_name
on table_name (column1, column2);

Example

Following query creates a composite index on the ID and Name columns of the above created table −

CREATE INDEX composite_index on CUSTOMERS (ID, Name);

Creating an Index Using Client Program

In addition to using SQL queries, we can also create an index on a table in a MySQL database using a client program.

Syntax

Following are the syntaxes to create an index in a MySQL database using various programming languages −

PHP NodeJS Java Python

The MySQL PHP connector mysqli provides a function named query() to execute the CREATE INDEX query in the MySQL database.

$sql=" CREATE INDEX index_name
   ON table_name (column_name)";
$mysqli->query($sql);

The MySQL NodeJS connector mysql2 provides a function named query() to execute the CREATE INDEX query in the MySQL database.

sql = "CREATE INDEX index_name
   ON table_name (column1, column2, ...)";
con.query(sql);

We can use the JDBC type 4 driver to communicate to MySQL using Java. It provides a function named executeUpdate() to execute the CREATE INDEX query in the MySQL database.

String sql = " CREATE INDEX index_name
   ON table_name (column_name)";
statement.executeUpdate(sql);

The MySQL Connector/Python provides a function named execute() to execute the CREATE INDEX query in the MySQL database.

create_index_query = CREATE INDEX index_name
   ON table_name (column_name [ASC|DESC], ...);
cursorObj.execute(create_index_query);

Example

Following are the implementations of this operation in various programming languages −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

// CREATE INDEX
$sql = "CREATE INDEX tid
        ON tutorials_table (tutorial_id)";
if ($mysqli->query($sql)) {
    printf("Index created successfully!.
");
}
if ($mysqli->errno) {
    printf("Index could not be created!.
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Index created successfully!.

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});
  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "create database TUTORIALS"
  con.query(sql);

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE temp(Name VARCHAR(255), age INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "INSERT INTO temp values('Radha', 29, 'Vishakhapatnam'), ('Dev', 30, 'Hyderabad');"
  con.query(sql);

  //Creating an Index
  sql = "CREATE INDEX sample_index ON temp (name);"
  con.query(sql);

  //Describing the table
  sql = "DESC temp;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {Field: 'Name',Type: 'varchar(255)',Null: 'YES',Key: 'MUL',Default: null,Extra: ''},
  {Field: 'age',Type: 'int',Null: 'YES',Key: '',Default: null,Extra: ''},
  {Field: 'Location',Type: 'varchar(255)',Null: 'YES',Key: '',Default: null,Extra: ''}
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CreateIndex {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Create an index on the tutorials_tbl...!;
         String sql = "CREATE INDEX tid ON tutorials_tbl (tutorial_id)";
         statement.executeUpdate(sql);
         System.out.println("Index created Successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Index created Successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
create_index_query = "CREATE INDEX idx_submission_date ON tutorials_tbl (submission_date)"
cursorObj.execute(create_index_query)
connection.commit()
print("Index created successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Index created successfully.
Advertisements