MySQL – Create Index
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 −
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 −
$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.