SQL INDEXES

CREATE [UNIQUE] [FULLTEXT] [SPATIAL] INDEX index_name
ON table_name (column1 [ASC|DESC], column2, ...)
[USING BTREE|HASH|GIST|SPGIST|GIN|BRIN]
[WITH (storage_parameter = value)]
[TABLESPACE tablespace_name]
[WHERE predicate];

2. Index Types Deep Dive

B-tree (Balanced Tree)

CREATE INDEX idx_customer_name ON customers(last_name, first_name);

Default for most databases. Optimal for range queries and equality comparisons.

Hash Index

CREATE INDEX idx_product_hash ON products USING HASH (product_code);

Only exact matches, no range scans. Faster than B-tree for equality but no sorting.

GIN (Generalized Inverted Index)

CREATE INDEX idx_document_content ON documents USING GIN (to_tsvector('english', content));

For composite values (arrays, JSON, full-text search).

BRIN (Block Range INdex)

CREATE INDEX idx_sensor_time ON sensor_data USING BRIN (reading_time);

For very large tables with naturally sorted data. Smaller but less precise.

-- PostgreSQL
CREATE INDEX idx_order_covering ON orders (customer_id) INCLUDE (order_date, total);

-- SQL Server
CREATE INDEX idx_order_covering ON orders(customer_id) INCLUDE (order_date, total);

-- PostgreSQL/SQLite
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- SQL Server
CREATE INDEX idx_active_users ON users(email) WHERE active = 1;

CREATE INDEX idx_name_lower ON users(LOWER(last_name));

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

CREATE CLUSTERED INDEX idx_orders_date ON orders(order_date)
WITH (DATA_COMPRESSION = PAGE, FILLFACTOR = 80);

CREATE BITMAP INDEX idx_products_category ON products(category_id)
TABLESPACE indx_ts;

-- PostgreSQL
REINDEX INDEX idx_orders_date;

-- SQL Server
ALTER INDEX idx_orders_date ON orders REBUILD;

-- MySQL
ANALYZE TABLE orders;

-- PostgreSQL
SELECT * FROM pg_stat_user_indexes;

-- SQL Server
SELECT * FROM sys.dm_db_index_usage_stats;

-- MySQL
SHOW INDEX FROM orders;

DROP INDEX IF EXISTS idx_old_data;

-- PostgreSQL example with covering index
CREATE INDEX idx_orders_covering ON orders (customer_id, order_date) 
INCLUDE (total_amount);

EXPLAIN ANALYZE 
SELECT customer_id, order_date, total_amount 
FROM orders 
WHERE customer_id BETWEEN 1000 AND 2000;

2. Composite Index Column Ordering

-- Optimal for queries filtering on customer_id and sorting by date
CREATE INDEX idx_customer_orders ON orders (customer_id, order_date DESC);

-- Versus less optimal ordering
CREATE INDEX idx_orders_customer ON orders (order_date, customer_id);

1. Leftmost prefix rule: Index (A,B,C) can be used for queries on A, (A,B), or (A,B,C)
2. Cardinality principle: Place high-selectivity columns first
3. Sorting priority: Columns used in ORDER BY should appear after equality filter columns
4. Workload analysis: Optimize for your most frequent query patterns

-- PostgreSQL JSONB indexing
CREATE INDEX idx_product_attributes ON products USING GIN (attributes);

-- SQL Server XML indexing
CREATE PRIMARY XML INDEX idx_xml_content ON docs(xml_column);
CREATE XML INDEX idx_xml_path ON docs(xml_column)
USING XML INDEX idx_xml_content FOR PATH;

-- PostgreSQL tsvector indexing
CREATE INDEX idx_document_search ON documents 
USING GIN (to_tsvector('english', content));

-- MySQL full-text index
CREATE FULLTEXT INDEX idx_product_description ON products(description)
WITH PARSER ngram;

-- SQL Server full-text catalog
CREATE FULLTEXT CATALOG ft_catalog AS DEFAULT;
CREATE FULLTEXT INDEX ON products(name, description) 
KEY INDEX pk_products ON ft_catalog;

-- PostGIS R-tree index
CREATE INDEX idx_geometries ON parcels USING GIST (geom);

-- SQL Server spatial index
CREATE SPATIAL INDEX idx_spatial_points ON locations(geography_point)
USING GEOGRAPHY_AUTO_GRID
WITH (BOUNDING_BOX = (-180, -90, 180, 90));

-- Oracle spatial index
CREATE INDEX idx_spatial_data ON spatial_table(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- PostgreSQL partitioned index
CREATE INDEX idx_partitioned_orders ON orders (order_date)
GLOBAL;  -- Or LOCAL for per-partition indexes

-- SQL Server partitioned index
CREATE INDEX idx_sales_date ON sales(order_date)
ON ps_monthly(order_date);

-- Oracle local partitioned index
CREATE INDEX idx_cust_orders_local ON orders(customer_id)
LOCAL TABLESPACE idx_ts;