Creating & Dropping Indexes
Creating the right indexes at the right time is a skill. You create indexes to solve specific slow queries. You drop indexes that are unused or on tables with heavy write load. Knowing the syntax and lifecycle of indexes is essential.
Index DDL Syntax
- CREATE INDEX name ON table(column) - basic index
- CREATE INDEX CONCURRENTLY - creates without locking the table (slower but safe in production)
- DROP INDEX name - removes an index
- CREATE UNIQUE INDEX - uniqueness enforced + indexed
- REINDEX - rebuilds a corrupt or bloated index
- ALTER TABLE ADD INDEX (MySQL syntax)
- Index names are database-scoped in PostgreSQL, table-scoped in MySQL
Creating & Dropping Examples
-- Create basic index on orders.user_id (FK without auto-index in PostgreSQL)
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Concurrent index creation (no table lock - safe for production)
CREATE INDEX CONCURRENTLY idx_orders_created ON orders(created_at);
-- Create index with INCLUDE (covering index - PostgreSQL)
CREATE INDEX idx_orders_user_covering ON orders(user_id)
INCLUDE (status, total_amount);
-- query can be answered from index alone (index-only scan)
-- Drop an index
DROP INDEX idx_orders_user_id;
-- MySQL syntax
CREATE INDEX idx_orders_user ON orders(user_id);
ALTER TABLE orders ADD INDEX idx_status (status);
DROP INDEX idx_orders_user ON orders;
-- REINDEX: rebuild a bloated/corrupt index
REINDEX INDEX idx_orders_user_id; -- single index
REINDEX TABLE orders; -- all indexes on table
-- Check index usage statistics (PostgreSQL)
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'orders';
-- idx_scan = 0 means the index was never used → consider dropping itQuick Quiz
Tip
Tip
Practice Creating Dropping Indexes in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Eliminate redundancy - each fact stored once. 3NF covers most real-world needs.
Common Mistake
Warning
A common mistake with Creating Dropping Indexes is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Creating Dropping Indexes from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Creating the right indexes at the right time is a skill.
- CREATE INDEX name ON table(column) - basic index
- CREATE INDEX CONCURRENTLY - creates without locking the table (slower but safe in production)
- DROP INDEX name - removes an index