Indexes help MySQL find rows faster by reducing the number of full-table scans. Without indexes, MySQL scans every row in the table for matching values, which is extremely slow on large datasets.

• Use indexes on columns used in WHERE, JOIN, ORDER BY, or GROUP BY.
• Avoid over-indexing — indexes slow down INSERT, UPDATE, and DELETE.

a. Index on a Single Column:-

General Syntax:- 

CREATE INDEX index_name ON table_name(column_name);

Example:-

CREATE TABLE users (

  id INT,

  name VARCHAR(100),

  email VARCHAR(100)

);

INSERT INTO users VALUES

(1, ‘Arun’, ‘arun@gmail.com’),

(2, ‘Riya’, ‘riya@yahoo.com’),

(3, ‘Ali’, ‘ali@gmail.com’);

Query:

SELECT * FROM users WHERE email = ‘ali@gmail.com’;

Output:-

b. For creating an index in MySQL:-

General Syntax:- 

CREATE INDEX idx_email ON users(email);

Example:-

CREATE UNIQUE INDEX idx_username ON users(name);

Attempt to Insert Duplicate:

INSERT INTO users VALUES (4, ‘Riya’, ‘riya2@yahoo.com’);

Output: (Error occurs as it prevents from inserting duplicate name values. )

ERROR 1062 (23000): Duplicate entry ‘Riya’ for key ‘idx_username’

c. Searching for users by email:

General Syntax:- 

SELECT * FROM users WHERE email = ‘john@example.com’;

Example:-

CREATE INDEX idx_name_email ON users(name, email);

Query:

SELECT * FROM users WHERE name = ‘Riya’ AND email = ‘riya@yahoo.com’;

Output:-