CodeCapsule

SQL CREATE INDEX statement

by

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 an INDEX 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

CREATE INDEX index_name
ON table_name (column1, column2, ...);

🔥 Example: Create Index on a Single Column

CREATE INDEX idx_users_email
ON users (email);

Speeds up queries like:

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

🔥 Composite Index (Multiple Columns)

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

Useful for queries such as:

SELECT * FROM orders
WHERE customer_id = 10
ORDER BY order_date;

Important: Column order matters.

🟦 MySQL Example

CREATE INDEX idx_products_name
ON products (name);

Unique index:

CREATE UNIQUE INDEX idx_users_username
ON users (username);

Fulltext index (MySQL-specific):

CREATE FULLTEXT INDEX idx_posts_content
ON posts(content);

🟪 PostgreSQL Example

CREATE INDEX idx_employees_lastname
ON employees (last_name);

Expression index:

CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

GIN index for JSONB:

CREATE INDEX idx_users_metadata_gin
ON users USING gin (metadata);

🟧 SQL Server Example

CREATE INDEX idx_orders_status
ON orders (status);

Include columns (SQL Server feature):

CREATE INDEX idx_orders_customer
ON orders (customer_id)
INCLUDE (order_date, total);

🟨 Oracle Example

CREATE INDEX idx_customers_city
ON customers(city);

Function-based index:

CREATE INDEX idx_users_upper_lastname
ON users (UPPER(last_name));

🔥 UNIQUE INDEX

Prevents duplicate values but also acts like a performance index.

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

🔧 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

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *