Sadiul Hakim

Posted on Oct 17

Database index tutorial with MYSQL

#database #index #mysql

1. What Is an Index?

An index in a database is like the index in a book — it helps you find data faster without scanning the entire table.

Without index → MySQL scans every row (Full Table Scan).
With index → MySQL jumps directly to matching rows using an internal data structure (usually a B-Tree).

In simple words:
Index = a data structure (B-Tree or Hash) that stores sorted key values and pointers to table rows.

2. Why Do We Need It?

Indexes speed up read operations such as:

SELECT with WHERE, JOIN, ORDER BY, GROUP BY
Searching/filtering large tables

They reduce disk I/O and CPU time.

Advantages:

Faster reads
Faster joins
Better query optimization

Disadvantages:

Slower writes (INSERT, UPDATE, DELETE) because index also needs updating
Consumes extra disk space
Too many indexes confuse the query planner

3. When to Use and When NOT to Use

Use Index When	Avoid Index When
Column used in `WHERE`, `JOIN`, `ORDER BY`, or `GROUP BY`	Column with low selectivity (e.g., gender = 'M' or 'F')
Frequently searched column (e.g., `email`, `username`)	Column that is frequently updated
Foreign keys and primary keys	Small tables (index overhead > benefit)
Range queries (`BETWEEN`, `<`, `>`)	Temporary or staging tables

Rule of Thumb:
Index columns that are used to filter or join large datasets.

4. How Index Works Internally (Simplified)

MySQL (InnoDB) stores data in B+Tree form:

        [50]
       /    \
   [20]      [70]
   /  \      /  \
[10][30]  [60][80]

The index keys are sorted.
Searching is O(log n) instead of O(n).
When you create an index on a column, MySQL maintains this structure automatically.

5. Types of Indexes in MySQL

Type	Description	Example
PRIMARY KEY	Unique identifier for each row	`id`
UNIQUE INDEX	Prevents duplicate values	`email`
INDEX (NON-UNIQUE)	For faster lookups	`name`, `created_at`
FULLTEXT INDEX	For text search (`MATCH ... AGAINST`)	`content`
COMPOSITE INDEX	Index on multiple columns	`(first_name, last_name)`
SPATIAL INDEX	For geo data (GIS)	`geometry`

6. Practical Examples (MySQL)

Let’s use a sample table:

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100),
  email VARCHAR(100),
  age INT,
  city VARCHAR(100),
  created_at DATETIME
);

6.1 Creating Indexes

-- Normal index
CREATE INDEX idx_username ON users(username);

-- Unique index
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- Composite index
CREATE INDEX idx_city_age ON users(city, age);

6.2 Dropping Index

DROP INDEX idx_username ON users;

6.3 Viewing Indexes

SHOW INDEX FROM users;

7. How to Measure Performance

Without Index

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

MySQL output shows:

type: ALL
rows: 1000000
Extra: Using where

Means full table scan.

With Index

After adding:

CREATE INDEX idx_email ON users(email);

Run again:

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Output:

type: ref
rows: 1
Extra: Using index

That means MySQL now uses the index and reads only matching rows.

8. Composite Index Guidelines

CREATE INDEX idx_name_city ON users(name, city);

The order of columns matters!

Query	Uses Index?
`WHERE name = 'John'`	✅ Yes
`WHERE name = 'John' AND city = 'Paris'`	✅ Yes
`WHERE city = 'Paris'`	❌ No (leftmost rule)

So always put the most selective column first in a composite index.

9. Common Mistakes

Too many indexes → slow writes, high maintenance.
Wrong order in composite index.
Indexing small or low-cardinality columns (e.g., gender, status).
Using function on indexed column breaks index usage:

   SELECT * FROM users WHERE LOWER(email) = 'john@example.com'; -- ❌ Index ignored

LIKE with leading wildcard disables index:

   WHERE username LIKE '%john%'  -- ❌ No index
   WHERE username LIKE 'john%'   -- ✅ Index used

10. Advanced Tips

Covering Index: If all selected columns are inside the index, MySQL doesn’t touch the table:

  CREATE INDEX idx_email_city ON users(email, city);
  SELECT email, city FROM users WHERE email = 'john@example.com';

→ Fully served from index.

Analyze and Optimize:

  EXPLAIN SELECT ...
  SHOW STATUS LIKE 'Handler_read%';

Remove unused indexes:

  SELECT * FROM sys.schema_unused_indexes;

11. Real-World Strategy

Purpose	Example
Primary key	`id`
Foreign key	Index on FK column
Search field	`username`, `email`, `phone`
Date filter	`(created_at)` or `(user_id, created_at)`
Sorting	Add `ORDER BY` columns in index

12. Summary Cheat Sheet

Task	SQL
Create index	`CREATE INDEX idx_col ON table(col);`
Unique index	`CREATE UNIQUE INDEX idx_col_unique ON table(col);`
Composite index	`CREATE INDEX idx_multi ON table(col1, col2);`
Drop index	`DROP INDEX idx_col ON table;`
View indexes	`SHOW INDEX FROM table;`
Check usage	`EXPLAIN SELECT ...`

DEV Community