Complete guide to CREATE INDEX across MySQL, PostgreSQL, SQLite, SQL Server, and Oracle. Covers single-column, composite, covering, and partial indexes.
What is a Database Index?
A database index is a data structure that improves the speed of data retrieval operations on a table. Think of it like the index at the back of a book — instead of scanning every page, the database can jump directly to the relevant rows.
Indexes come with trade-offs: they speed up SELECT queries but slow down INSERT, UPDATE, and DELETE operations because the index must be maintained. They also consume disk space. The key is to index the right columns — primarily those used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
Basic CREATE INDEX Syntax
CREATE INDEX index_name ON table_name(column_name);
Types of Indexes
B-tree (default): Balanced tree structure. Great for equality and range queries.
Hash: Only useful for exact equality lookups. Not supported by all storage engines.
Full-text: Optimized for text search (MATCH ... AGAINST in MySQL).
Composite: Covers multiple columns. Order matters — put the most selective column first.
Covering: Contains all columns needed by a query, eliminating table lookups.
Partial / Filtered: Indexes only rows matching a condition (PostgreSQL, SQL Server).
⚡ CREATE INDEX — All Dialects
Copy-paste ready index creation scripts with composite, covering, and online build options.
-- Single-column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_users_name_status ON users(name, status);
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Full-text index (MyISAM or InnoDB 5.6+)
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);
-- Online index build (InnoDB, MySQL 5.6+)
ALTER TABLE users
ADD INDEX idx_users_email (email),
ALGORITHM=INPLACE, LOCK=NONE;
-- Prefix index (for long VARCHAR columns)
CREATE INDEX idx_users_email_prefix ON users(email(50));
-- Single-column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_users_name_status ON users(name, status);
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Partial / filtered index
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- Concurrent index build (no table lock)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Covering index with INCLUDE (PostgreSQL 11+)
CREATE INDEX idx_users_email ON users(email) INCLUDE (name, created_at);
-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- GIN index for JSONB / arrays
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- Single-column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_users_name_status ON users(name, status);
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Covering index (SQLite 3.8.2+ supports covering indexes)
CREATE INDEX idx_users_email_name ON users(email, name);
-- Index on expression
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Drop index
DROP INDEX idx_users_email;
-- Single-column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_users_name_status ON users(name, status);
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Covering index with INCLUDE
CREATE INDEX idx_users_email ON users(email) INCLUDE (name, created_at);
-- Online index build
CREATE INDEX idx_users_email ON users(email) WITH (ONLINE = ON);
-- Filtered index
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- Columnstore index (for analytics workloads)
CREATE CLUSTERED COLUMNSTORE INDEX cci_orders ON orders;
-- Single-column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_users_name_status ON users(name, status);
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Function-based index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Bitmap index (for low-cardinality columns)
CREATE BITMAP INDEX bmp_users_status ON users(status);
-- Online index build
CREATE INDEX idx_users_email ON users(email) ONLINE;
-- Reverse key index (for sequential inserts)
CREATE INDEX idx_orders_id ON orders(id) REVERSE;
⚠️ Safety Note: Adding an index on a large table can lock the table and consume significant disk I/O. On PostgreSQL, always use CREATE INDEX CONCURRENTLY in production. On MySQL, use ALGORITHM=INPLACE, LOCK=NONE. On SQL Server, use WITH (ONLINE = ON). Schedule index builds during low-traffic windows when possible.
💡 Index Naming Convention: Use idx_<table>_<column> for single-column indexes and idx_<table>_<col1>_<col2> for composite indexes. This makes it easy to identify which indexes are safe to drop when optimizing.