Developer Snippet Diary

Indexes, Unique, Primary key in Database Mysql

DATABASE Updated: September 11, 2025 Author: R GONDAL Public

Indexes in MySQL are used to make queries faster. They work like a book’s index: instead of reading every page. MySQL must scan the entire table row by row In normal.

???? What is an Index?

  1. A data structure (usually a B-Tree) that MySQL maintains for quick lookup. MySQL can jump directly to relevant rows.

???? Types of index?

1.Normal Index 2. Unique Index
Allows duplicate values. Ensures that values in the column are unique (like a constraint). ERROR if insert duplicates
Useful for: Best for columns that you frequently use in WHERE, JOIN, or ORDER BY. Useful for: emails, usernames, national IDs, etc. 
CREATE INDEX idx_name ON users(name);
ALTER TABLE users ADD INDEX idx_name (name);
 
CREATE UNIQUE INDEX uniq_email ON users(email);
ALTER TABLE users ADD UNIQUE INDEX uniq_email (email);

 

3. Primary Key (special unique index) 4. Fulltext Index

Every table usually has one primary key.

It’s a unique index that also identifies each row.

 Special index for searching words inside text. Works with MATCH() AGAINST().
Best for articles, reviews, descriptions.
 
ALTER TABLE users ADD PRIMARY KEY (id);
 
ALTER TABLE reviews ADD FULLTEXT idx_title (title);
SELECT * FROM reviews 
WHERE MATCH(title) AGAINST('coffee shop' IN NATURAL LANGUAGE MODE);

 

5.Composite Index
An index on multiple columns. 
CREATE INDEX idx_name_city ON users(name, city);
SELECT * FROM users WHERE name='John' AND city='Paris';

Example (1 million rows):

Query Without Index With Index
WHERE id=123 1-2 seconds 1-2 mili seconds
WHERE email='abc@gmail.com' 1 s 1-3ms
WHERE name LIKE "abc%" 1 s 5-10ms
WHERE name LIKE "%abc%" 1s 1s (Index not use if %query%)
 
 
How to Delete Indexes
-- Drop index created with CREATE INDEX or ALTER TABLE ADD INDEX
DROP INDEX idx_name ON users;

-- Drop unique index
ALTER TABLE users DROP INDEX uniq_email;

-- Drop primary key
ALTER TABLE users DROP PRIMARY KEY;

 

How to Check Indexes

SHOW INDEXES FROM users;

Key_name → index name
Non_unique → 0 = unique, 1 = not unique
Seq_in_index → column order in composite index

Use EXPLAIN to check if MySQL is actually using your index:

EXPLAIN SELECT * FROM users WHERE email = 'abc@test.com';

 Summary:

INDEX = speed booster.

UNIQUE INDEX = speed booster + ensures no duplicates.

FULLTEXT = for searching words.

Indexes can make queries run in milliseconds instead of seconds, especially on large tables.

Export Database:

// Generate Schema-Only Dump (including Primary & Unique Keys)
mysqldump -u rizi -p --no-data reviewsfunda > schema_only.sql 

// export only data insert commands
mysqldump -u rizi -p --no-create-info reviewsfunda > data_only.sql 

# Get a full schema dump including all index definitions
mysqldump -u rizi -p --no-data --skip-add-drop-table reviewsfunda > full_schema_with_indexes.sql

Import Database:

 

Posted by: R GONDAL
Email: rizikmw@gmail.com