Index Commands in SQL

Database has solved the problem of storing large amount of data for us. However the key requirement is also that when we retrieve information from a database it needs to be quick. One of the ways databases achieve this is via indexes. In this post we are going to learn about indexes in database.

An index is nothing but a special lookup table which stores value of index column in a sorted manner and then a pointer to actual location of the row in the original table. This makes any kind of data retrieval very fast. They are useful in sorting data and give much better performance.

Now let’s learn how to create indexes.

Indexes can be created while you are creating the table itself or can be created after the table is created.

To create it along with the table the syntax is as given, let’s use it in an example.

In the create table command, will have the keyword index followed by index name and then the column name on which index is to be created in brackets. You can also specify ascending or descending order.

To create it on an already existing table we use the CREATE INDEX command and then give the index name and then give ON which table we want the index followed by specific column names in bracket.

If you use the UNIQUE key word while creating the indexes, it does not allow multiple same values in the table.

To remove an index we use the DROP INDEX command with the index name and on which table it needs to be dropped.

Alternatively you can also use DROP index in ALTER Table command which also serves the same purpose.

To anytime view the indexes on a table we can use SHOW INDEXES command from the table. It will showcase all indexes that exists for the table.

Do note that Indexes are automatically created for primary key constraints and unique constraints.

While indexes are useful, they have some disadvantages too: that performance of update, insert and delete decreases. This is because any update to table also needs update to index table too. These index tables also consume space and more the indexes and more the data in the table, more space they consume. So it is advised that indexes are always created when they are actually needed.

General guidelines is that index should be created when a column contains wide range of values, is typically not null and it is frequently used in search conditions.

And it should be avoided if the table is small and if columns are not typically used for search condition or column is updated too frequently.

Check out our video: https://youtu.be/0qv7oXlD0uk