MySQL - Non-Clustered Index

---

---

Indexes in MySQL are used to retrieve the data much faster from the database tables or views. Users cannot see the indexes on the application level, but they work behind to speed up searches and queries.

There are two types of Indexes in MySQL −

• Clustered Index

• Non-Clustered Index

A clustered index in MySQL can sort the data in a table manually by ordering all the rows in the table based on the key columns used to create it. On the other hand, a non-clustered index stores data in one location and indexes containing pointers to this data in another location.

MySQL Non-Clustered Indexes

Non-Clustered indexes store data in one location and its indexes in another location. These indexes contain pointers to the actual data.

However, MySQL does not provide ways to explicitly create clustered and non-clustered indexes. A PRIMARY KEY is treated as a clustered index. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is a clustered index. All the other indexes on a table are non-clustered indexes.

Syntax

Following is the basic syntax to create a non-clustered index on a MySQL table −

CREATE INDEX index_name ON table_name(column_name(s));

Example

Let us see an example to create a non-clustered index on a table named 'Students'. This table contains details of students like their Roll Number, Name, Age, and Department. Here, we are trying to apply the non-clustered index on columns Roll Number and Department, using the following query −

Let us first create the table Students using CREATE TABLE statement shown below −

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

Using the following query, create a non-clustered index on the NAME column −

CREATE INDEX nc_index ON CUSTOMERS(NAME);

Note − As MySQL does not have specific provision for Non-Clustered Index, we are using the usual CREATE INDEX statement.

Verification

To verify whether the INDEX is created on the table CUSTOMERS or not, display the table definition using DESC command −

DESC CUSTOMERS;

As we can see below, there are two indexes created on the CUSTOMERS table. The PRIMARY KEY index is a clustered index and the multi-index is a non-clustered index −

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

Creating a Non-Clustered Index Using NodeJS

In addition to using SQL queries to create non-clustered indexes, we can also create them on a MySQL database using a client program.

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

Syntax

Following is the syntax to create a non-clustered index in MySQL database using NodeJS −

sql = "CREATE INDEX index_name ON table_name(column_name(s))";
con.query(sql);

Example

Following are the implementation of this operation using NodeJS −

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("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE STUDENTS(RNO INT NOT NULL,NAME VARCHAR(50),AGE INT,DEPT VARCHAR(50));"
  con.query(sql);

  //Creating Index
  sql = "CREATE INDEX nc_index ON STUDENTS(RNO, DEPT);"
  con.query(sql);

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

Output

The output produced is as follows −

Connected!
--------------------------
[
  {Field: 'RNO',Type: 'int',Null: 'NO',Key: 'MUL',Default: null,Extra: ''},
  {Field: 'NAME',Type: 'varchar(50)',Null: 'YES',Key: '',Default: null,Extra: ''},
  {Field: 'AGE',Type: 'int',Null: 'YES',Key: '',Default: null,Extra: ''},
  {Field: 'DEPT',Type: 'varchar(50)',Null: 'YES',Key: '',Default: null,Extra: ''}
]