How to create an index with SQL statement

4 years ago
Zaid Bin Khalid
2189 Views
5

In this session, you will deeply learn how to improve the database performance by creating an index with the SQL statement and example on the table.

What is Index?

It is a database structure that provides fast access to rows in a table that is based on the values in one or more columns.

Let’s suppose you have a table which is called the customers’ table in your database and you want to find all the customers’ detail whose names begin with the letter A, use the Following statement as mention below.

SELECT cust_id, cust_name, address FROM customers 
WHERE cust_name LIKE 'A%';

To find such customers’ detailed data in the server, they must scan each row one by one in the customers’ table and check the contents of the name column. It might work great in a table that has few rows but supposes how long it will take to answer the table that contains millions of rows. In this type of situation, to get more speed, you can apply indexes to the table.

Creating an Index.

You can create a new index with the help of this statement CREATE INDEX.

CREATE INDEX index_name ON table_name (column_name);

For Instance, you can use the mention below statement to create an index on the name column in the customers’ table.

CREATE INDEX cust_name_idx ON customers (cust_name);

The index will allow duplicate entries while sort them in the ascending order by default. To add unique entries in the index, Add the keyword UNIQUE after CREATE as shown.

CREATE UNIQUE INDEX cust_name_idx 
ON customers (cust_name);

Creating Multi-column Indexes.

You can also build the indexes that support multiple columns. For example, let assume that you have a table in your database named users having the columns first_name and last_name, and you easily access the user’s records by using these columns to improve the performance you can build an index on both columns as shown:

CREATE INDEX user_name_idx ON users (first_name, last_name);

The Downside of Indexes.

The index must be created very carefully because all indexes on that table must be modified whenever a row added, updated, or removed from a table. When you have more indexes, serves needs to work more. Which gives a slow performance.

Here is some basic rule which you can follow while creating an index.