MySQL | A short note on database indexes

|
| By Webner

Index is used for storing values for a specific column in a table. An index is created on a column of a table and used to speed up search queries.

For Example :

Suppose we have a database table called Users with three columns – User_Name, User_Age, and User_Address. Assume that this table has thousands of rows and we want to find all the details of users named ‘karan’. Query for achieving this :

SELECT * FROM Users  WHERE User_Name = 'karan';

Database will look at every row in Users table to see if the User_Name for that row is ‘karan’. The number of results can be more than one and we want every row named ‘karan’ . To speed up search query, we can use Index for column User_Name.

Syntax to create Index :

CREATE INDEX index_name ON Users (User_Name);

Before using Index, database scans full table for searching User_name ‘Karan’ . After using index, database cuts down the number of rows and examines only one column. It’s important to note that the index stores data in sorted form (alphabetically)

We can use indexing on multiple columns by using the following query :

CREATE INDEX index_name ON Users (User_Name,User_age);

Relation with Delete, update and Insert query
Though using index with Select query can speed up search query but using insert, update and delete query can slow performance because whenever you insert, delete, or update rows in the table, the same operations will have to be done to index.

For Example:

In users table, there are five thousand rows and we want to delete three thousand rows at a time. Operations that delete rows will be slow, as it will have to update the index after deleting the records which can take time especially when there are a lot of records in the table.

Webner Solutions is a Software Development company focused on developing CRM apps (Salesforce, Zoho), LMS Apps (Moodle/Totara), Websites and Mobile apps. If you need Web development or any other software development assistance please contact us at webdevelopment@webners.com

Leave a Reply

Your email address will not be published. Required fields are marked *