How To Create Index in MySQL

MySQL Indexes make it easy to do lookups and improve SQL query speed. Here’s how to create index in MySQL to improve database performance, using MySQL CREATE INDEX statement.

How Indexing Works in MySQL

Typically, when you run an SQL query with a WHERE clause or other kind of lookups such as for JOINS, MySQL needs to scan all records in your table to find matching rows. In case your tables has millions of rows, this can be very time consuming and slow down your SQL queries considerably.

MySQL Indexes speed up your SQL queries by maintaining the indexed columns values in a B-tree structure which is optimized for lookups and matching. So indexes make it easier to quickly find rows that match one or more values.

By default, MySQL create index for every primary key column in your database.

Bonus Read : How to Create Stored Procedure in MySQL

How To Create Index in MySQL

Here are the steps to create index in MySQL. You can either create index at the time of table creation, or add it afterwards. We will look at both these approaches.

How to Create MySQL index during table creation

Here’s the syntax to create index during table creation

CREATE TABLE table_name(
   column_definitions,
   INDEX (list_of_columns) 
);

In the above query, table_name is your table to be created and column_definitions is the list of columns and definitions.

INDEX(list_of_columns) is where you create MySQL index. The index will be created for the comma separated list of columns mentioned as list_of_columns

Bonus Read : How to Create User in MySQL

Here’s an example.

CREATE TABLE orders(
   order_id INT PRIMARY KEY,
   sale INT NOT NULL,
   product_id INT NOT NULL,
   product_name VARCHAR(10),
   INDEX (product_id) 
);

In the above query, we have created Index for product_id column.

Here’s an example of how to create index using multiple columns.

CREATE TABLE orders(
   order_id INT PRIMARY KEY,
   sale INT NOT NULL,
   product_id INT NOT NULL,
   product_name VARCHAR(10),
   INDEX (product_id, order_id) 
);

In the above query, we have created index for multiple columns product_id, order_id

Bonus Read : How to Create Database in MySQL

How to Add Index to Column in Table

Let’s say you want to add index for column or set of columns, then use CREATE INDEX statement. Here’s the syntax of CREATE INDEX statement

CREATE INDEX index_name ON table_name (column_list)

In the above query, we specify the index name, the table for which you need to create index, and list of columns to be included in indexing.

Here’s an example to add new index for column product_name in the above orders table

CREATE INDEX prod_name_index ON orders (product_name);

To view the indexes added to a table, you can use SHOW INDEXES statement. Here’s the syntax for SHOW INDEXES

SHOW INDEXES from table_name;

Here’s an example to view indexes from orders table
SHOW INDEXES from orders;

Hopefully, the above article will help you create index in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.