SQL CREATE INDEX statement

SQL Tutorial

Here is a clear, practical, and complete guide to the SQL CREATE INDEX statement, including examples for MySQL, PostgreSQL, SQL Server, and Oracle, plus performance best practices.

✅ What SQL CREATE INDEX statement Does

An index speeds up data retrieval by creating a separate, optimized data structure (usually a B-tree) pointing to rows in a table.

Indexes improve performance for:

  • WHERE filters

  • JOIN conditions

  • ORDER BY

  • GROUP BY

  • Searching text fields (with special index types)

However, indexes:

  • Slow down INSERT/UPDATE/DELETE

  • Take disk space

🔹 Basic Syntax

🔥 Example: Create Index on a Single Column


Speeds up queries like:


🔥 Composite Index (Multiple Columns)


Useful for queries such as:


Important: Column order matters.

🟦 MySQL Example


Unique index:


Fulltext index (MySQL-specific):


🟪 PostgreSQL Example


Expression index:


GIN index for JSONB:


🟧 SQL Server Example


Include columns (SQL Server feature):


🟨 Oracle Example


Function-based index:


🔥 UNIQUE INDEX

Prevents duplicate values but also acts like a performance index.


🔧 DROP INDEX

MySQL

DROP INDEX idx_users_email ON users;

PostgreSQL

DROP INDEX idx_users_email;

SQL Server

DROP INDEX idx_users_email ON users;

Oracle

DROP INDEX idx_users_email;

📌 When Should You Create an Index?

✔ Create an index when a column is:

  • Frequently used in WHERE filters

  • Used in JOIN conditions

  • Used for sorting (ORDER BY)

  • Used for grouping (GROUP BY)

  • A foreign key

  • Frequently searched text (full-text indexes)

❌ Avoid indexing:

  • Low-cardinality columns (e.g., gender, is_active)

  • Very small tables

  • Columns frequently updated with large strings

🧠 Index Order Matters in Composite Indexes

For:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Works for:

  • WHERE customer_id = ?

  • WHERE customer_id = ? AND order_date > ?

  • ORDER BY customer_id, order_date

Does not work efficiently for:

  • WHERE order_date = ? (skips first column)

🏎 Index Performance Tips

✔ Index foreign key columns
✔ Index columns used in JOINs
✔ Avoid creating too many indexes (each slows writes)
✔ Use composite indexes when queries filter on multiple columns
✔ Monitor index usage (EXPLAIN, EXPLAIN ANALYZE)
✔ Consider partial indexes (PostgreSQL) when data is sparse
✔ Use UNIQUE when appropriate for integrity + performance

You may also like...