logo
Back
Share
Share to LinkedIn
Share to Facebook
Share to Twitter
Share to Hacker News
Share to Telegram
Table of Contents
DatabaseMySQLPostgres

Mastering Database Indexes: A Complete Guide to SQL Indexes in PostgreSQL, MySQL, and MariaDB

October 15, 2025
2 min read
36 views
Mastering Database Indexes: A Complete Guide to SQL Indexes in PostgreSQL, MySQL, and MariaDB

Deep dive into database indexes with practical examples and performance insights. Learn how to create, optimize, and manage indexes across PostgreSQL, MySQL, and MariaDB. Includes detailed code examples, best practices, and real-world scenarios to boost query performance by up to 100x.

Introduction

Database indexes are one of the most powerful tools for optimizing query performance. Understanding when and how to use indexes can transform slow queries into lightning-fast operations. This comprehensive guide covers index fundamentals, types, and implementation across major SQL databases.

What Are Database Indexes?

Indexes are data structures that improve the speed of data retrieval operations on database tables. Think of them like a book's index - instead of reading every page to find a topic, you jump directly to the relevant pages.

Basic Concept Example:

Code
1-- Without index: Full table scan 2SELECT * FROM users WHERE email = 'john@example.com'; 3-- Scans all 1,000,000 rows 4 5-- With index on email column 6CREATE INDEX idx_users_email ON users(email); 7-- Now searches through index tree structure 8-- Reduces search from 1,000,000 to ~20 operations (log2(n))

Types of Indexes

1. Single-Column Index

Code
1-- PostgreSQL/MySQL/MariaDB (similar syntax) 2CREATE INDEX idx_user_lastname ON users(last_name); 3 4-- Using the index 5SELECT * FROM users WHERE last_name = 'Smith';

2. Composite (Multi-Column) Index

Code
1-- Order matters! Index on (last_name, first_name) 2CREATE INDEX idx_user_name ON users(last_name, first_name); 3 4-- Efficient queries: 5SELECT * FROM users WHERE last_name = 'Smith'; 6SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John'; 7 8-- Inefficient (doesn't use index): 9SELECT * FROM users WHERE first_name = 'John';

3. Unique Index

Code
1-- PostgreSQL/MySQL/MariaDB 2CREATE UNIQUE INDEX idx_users_email ON users(email); 3 4-- Alternative using constraint 5ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

4. Partial Index (PostgreSQL)

Code
1-- PostgreSQL only 2CREATE INDEX idx_active_users ON users(created_at) 3WHERE status = 'active'; 4 5-- Efficient query 6SELECT * FROM users 7WHERE status = 'active' AND created_at > '2025-01-01';

5. Full-Text Index

PostgreSQL:

Code
1-- Create GIN index for full-text search 2CREATE INDEX idx_posts_content ON posts 3USING GIN(to_tsvector('english', content)); 4 5-- Search query 6SELECT * FROM posts 7WHERE to_tsvector('english', content) @@ to_tsquery('database & index');

MySQL/MariaDB:

Code
1-- Create FULLTEXT index 2CREATE FULLTEXT INDEX idx_posts_content ON posts(content); 3 4-- Search query 5SELECT * FROM posts 6WHERE MATCH(content) AGAINST('database index' IN NATURAL LANGUAGE MODE);

Database-Specific Index Types

PostgreSQL Specific

B-tree Index (default):

Code
1CREATE INDEX idx_users_age ON users(age);

Hash Index:

Code
1CREATE INDEX idx_users_id_hash ON users USING HASH(id); 2-- Good for equality comparisons only

GiST Index (Geometric):

Code
1CREATE INDEX idx_locations ON stores USING GIST(location); 2-- For spatial data, ranges, and custom types

GIN Index (Inverted):

Code
1CREATE INDEX idx_tags ON articles USING GIN(tags); 2-- For JSONB, arrays, and full-text search

MySQL/MariaDB Specific

Spatial Index:

Code
1CREATE SPATIAL INDEX idx_location ON stores(coordinates); 2-- For GEOMETRY, POINT, LINESTRING types

Descending Index (MySQL 8.0+):

Code
1CREATE INDEX idx_created_desc ON posts(created_at DESC);

Viewing Existing Indexes

PostgreSQL:

Code
1-- All indexes on a table 2SELECT indexname, indexdef 3FROM pg_indexes 4WHERE tablename = 'users'; 5 6-- Detailed index information 7\d+ users

MySQL/MariaDB:

Code
1-- Show all indexes 2SHOW INDEX FROM users; 3 4-- Detailed index information 5SHOW CREATE TABLE users;

Analyzing Index Usage

PostgreSQL:

Code
1-- Check query execution plan 2EXPLAIN ANALYZE 3SELECT * FROM users WHERE email = 'john@example.com'; 4 5-- Index usage statistics 6SELECT 7 schemaname, tablename, indexname, 8 idx_scan, idx_tup_read, idx_tup_fetch 9FROM pg_stat_user_indexes 10WHERE tablename = 'users';

MySQL/MariaDB:

Code
1-- Check query execution plan 2EXPLAIN 3SELECT * FROM users WHERE email = 'john@example.com'; 4 5-- Force index usage 6SELECT * FROM users 7FORCE INDEX (idx_users_email) 8WHERE email = 'john@example.com';

Dropping and Rebuilding Indexes

Code
1-- PostgreSQL/MySQL/MariaDB 2DROP INDEX idx_users_email; 3 4-- PostgreSQL - rebuild index 5REINDEX INDEX idx_users_email; 6 7-- MySQL/MariaDB - optimize table (rebuilds indexes) 8OPTIMIZE TABLE users;

Real-World Performance Example

Scenario: E-commerce Order Query

Code
1-- Create orders table 2CREATE TABLE orders ( 3 id SERIAL PRIMARY KEY, 4 user_id INT, 5 status VARCHAR(20), 6 created_at TIMESTAMP, 7 total_amount DECIMAL(10,2) 8); 9 10-- Insert sample data (1 million records) 11-- Without indexes - slow query 12SELECT * FROM orders 13WHERE user_id = 12345 14AND status = 'completed' 15AND created_at > '2025-01-01'; 16-- Execution time: 2.5 seconds 17 18-- Add composite index 19CREATE INDEX idx_orders_lookup 20ON orders(user_id, status, created_at); 21 22-- Same query with index 23-- Execution time: 0.02 seconds (125x faster!)

Best Practices

Create indexes on:

•
Foreign key columns
•
Columns frequently used in WHERE clauses
•
Columns used in JOIN conditions
•
Columns used in ORDER BY and GROUP BY

Avoid indexing:

•
Small tables (< 1000 rows)
•
Columns with low cardinality (few unique values)
•
Columns frequently updated
•
Very wide columns (large text fields)

Composite Index Strategy:

Code
1-- Good: Most selective column first 2CREATE INDEX idx_search ON products(category_id, price, name); 3 4-- Query patterns this supports: 5-- ✓ WHERE category_id = 1 6-- ✓ WHERE category_id = 1 AND price > 100 7-- ✓ WHERE category_id = 1 AND price > 100 AND name LIKE 'A%' 8-- ✗ WHERE price > 100 (doesn't use index efficiently)

Common Index Pitfalls

Using Functions on Indexed Columns:

Code
1-- Bad: Function prevents index usage 2SELECT * FROM users WHERE LOWER(email) = 'john@example.com'; 3 4-- Good: Store lowercase or use functional index 5-- PostgreSQL 6CREATE INDEX idx_email_lower ON users(LOWER(email));

Implicit Type Conversion:

Code
1-- Bad: String column compared to number 2SELECT * FROM users WHERE phone_number = 1234567890; 3 4-- Good: Use proper type 5SELECT * FROM users WHERE phone_number = '1234567890';

Monitoring Index Health

PostgreSQL - Find Unused Indexes:

Code
1SELECT 2 schemaname, tablename, indexname, idx_scan 3FROM pg_stat_user_indexes 4WHERE idx_scan = 0 5AND indexrelname NOT LIKE 'pg_toast%';

MySQL/MariaDB - Check Index Statistics:

Code
1SELECT 2 TABLE_NAME, INDEX_NAME, CARDINALITY 3FROM information_schema.STATISTICS 4WHERE TABLE_SCHEMA = 'your_database' 5ORDER BY TABLE_NAME, SEQ_IN_INDEX;

Conclusion

Proper index design is crucial for database performance. Start with the most frequently used queries, analyze execution plans, and create indexes strategically to optimize performance. Remember that indexes have trade-offs - they speed up reads but slow down writes. Regular monitoring and maintenance ensure your indexes remain effective as your data grows.

Tags

DatabaseMySQLPostgres