HintsToday

Here’s a comprehensive, logically structured, and interactive guide to SQL Indexing, consolidating and enhancing all the content you’ve shared, complete with real examples, platform-specific insights, and advanced use cases:

🧠 Mastering Indexing in SQL: A Complete Guide

🔍 What is Indexing in SQL?

Indexing is a performance optimization technique that allows fast retrieval of rows from a table, similar to a book index that helps locate topics quickly.

🎯 Core Idea

Instead of scanning every row (full table scan), the database engine can jump directly to matching rows using a sorted data structure like a B-tree or hash table.

⚙️ How Does Indexing Work?

1. Index Creation: You specify one or more columns to index.
2. Index Structure: Data is stored in a B-tree, bitmap, hash, or other structures depending on DB type.
3. Query Execution: On query, the DB engine checks the query plan and uses the index if available and efficient.
4. Row Lookup: Index holds pointers (row IDs or offsets) to locate the actual data.

📂 Types of Indexes (with Syntax & Use Cases)

🚀 When to Use Indexes (Use Cases)

✅ Best Use Cases for Indexes

⚠️ Cases Where Indexes May Not Help

• Small tables: full scan is often faster.
• Columns with low cardinality: e.g., gender = ‘M/F’.
• Queries without WHERE/JOIN/ORDER BY.
• Filtering by non-leading column of composite index.

🔨 Creating Indexes — Syntax Examples

-- Basic Index
CREATE INDEX idx_email ON employees(email);

-- Composite Index
CREATE INDEX idx_email_dept ON employees(email, department);

-- Unique Index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Function-based Index (PostgreSQL)
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- Partial Index (PostgreSQL)
CREATE INDEX idx_active_users ON users(last_login) WHERE is_active = true;

-- Full-text Index (MySQL, PostgreSQL)
CREATE FULLTEXT INDEX idx_text_content ON documents(content);

-- Drop Index
DROP INDEX idx_email; -- PostgreSQL
DROP INDEX idx_email ON employees; -- MySQL

🔬 Platform-Specific Hands-On Demos

🐘 PostgreSQL Indexing Demo

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  city VARCHAR(50),
  created_at DATE
);

-- Insert sample data
INSERT INTO customers (name, email, city, created_at)
SELECT 
  'User' || i, 
  'user' || i || '@example.com', 
  CASE WHEN i % 2 = 0 THEN 'Delhi' ELSE 'Mumbai' END,
  CURRENT_DATE - (i || ' days')::interval
FROM generate_series(1, 100000) AS i;

-- Create index
CREATE INDEX idx_city ON customers(city);

-- Analyze performance
EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Delhi';

🐬 MySQL Indexing Demo

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2),
  order_date DATE
);

-- Insert test data using procedure
DELIMITER $$
CREATE PROCEDURE insert_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 100000 DO
    INSERT INTO orders (customer_id, amount, order_date)
    VALUES (
      FLOOR(RAND()*1000),
      RAND()*1000,
      DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*365) DAY)
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL insert_orders();

-- Index and performance check
CREATE INDEX idx_order_date ON orders(order_date);
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-01';

🔥 PySpark + Delta Lake Z-Ordering

# Create delta table
from pyspark.sql.functions import expr
df = spark.range(0, 1000000).withColumn("order_id", expr("id % 1000")) \
                             .withColumn("country", expr("CASE WHEN id % 2 = 0 THEN 'India' ELSE 'US' END")) \
                             .withColumn("amount", expr("rand() * 1000"))

df.write.format("delta").mode("overwrite").save("/mnt/delta/orders")
spark.sql("CREATE TABLE delta_orders USING DELTA LOCATION '/mnt/delta/orders'")

# Optimize with Z-Ordering
spark.sql("OPTIMIZE delta_orders ZORDER BY (country)")

# Query performance
spark.sql("SELECT * FROM delta_orders WHERE country = 'India'").explain(True)

📈 How to Check if Index is Used

• PostgreSQL: EXPLAIN ANALYZE SELECT ... → Look for Index Scan or Bitmap Index Scan
• MySQL: EXPLAIN SELECT ... → Look for Using index or ref
• Spark: Use .explain(True) or Query Execution Plan

📉 Disadvantages of Indexing

🧠 Advanced Tips & Best Practices

• Start with frequently queried columns (WHERE/JOIN).
• Use composite indexes carefully – Always filter using left-most column.
• Don’t index everything – Focus on high-cardinality, high-impact queries.
• Regularly analyze and drop unused indexes – Monitor via query plans.
• Monitor index usage – Most RDBMSs provide pg_stat_user_indexes, SHOW INDEX, or query performance tables.

🧊 Final Summary