How To Create Indexes 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 tableSHOW 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!

About Author