Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

Everything about sql index

Why do we need index?

The index helps to do fast searching. Suppose you have a table. Where you have columns like name, age, and gender. To search in the table, always use that column by which you can uniquely identify each records. In this example, we will use name column. Now if you make the name column as index column then your searching will be more fast and accurate.

In mysql to create index, INDEX command is used.

How to create index?

CREATE INDEX index_column_name
ON table_name(columns name);

How to delete index?

DROP INDEX index_column_name ON table_name;

Guidelines to create index

1. Index those columns which are mostly used for searching.
2. Don't put those columns in the index which contain too many null values.
3. Use that columns in index that is used for join tables. to improve join performance.
4. Don't create index in a small table.

Let's see an example:

Suppose you have a database name std and inside that database, you have a table named student and in the table, you have columns like name,date_of_birth, gender, and class. Now let's make student date_of_birth as the index

CREATE INDEX student_date_of_birth
ON student(date_of_birth);

You can use multiple columns for the index.
CREATE INDEX student_date_of_birth
ON student(date_of_birth,name);

How to see an existing table index column name?

SHOW INDEX FROM table_name;

SHOW INDEX FROM student;

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us