Indexes and Views

-- Basic index on one column
CREATE INDEX idx_users_email ON users(email);

-- Multi-column (composite) index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Unique index (enforces uniqueness like a UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);

-- Named index
CREATE INDEX idx_products_category ON products(category_id);

-- IF NOT EXISTS (PostgreSQL 9.5+)
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Index on expression
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Query must use the same expression: WHERE LOWER(email) = 'alice@example.com'

-- Partial index (indexes only a subset of rows)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Useful when queries almost always filter on that condition

-- Index including extra columns (covering index — PostgreSQL INCLUDE)
CREATE INDEX idx_orders_user ON orders(user_id)
INCLUDE (total, status);
-- Allows index-only scans for: SELECT total, status FROM orders WHERE user_id = ?

-- Concurrent index build (PostgreSQL — no table lock)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- PostgreSQL
DROP INDEX idx_users_email;
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock

-- MySQL (must specify table)
DROP INDEX idx_users_email ON users;
ALTER TABLE users DROP INDEX idx_users_email;

-- SQL Server
DROP INDEX idx_users_email ON users;

-- PostgreSQL: specify index type
CREATE INDEX idx_products_name_gin
    ON products USING GIN (to_tsvector('english', name));

CREATE INDEX idx_users_data_jsonb
    ON users USING GIN (metadata);

CREATE INDEX idx_events_ts_brin
    ON events USING BRIN (created_at);

-- MySQL: full-text index
CREATE FULLTEXT INDEX idx_ft_products ON products(name, description);

-- MySQL: spatial index
CREATE SPATIAL INDEX idx_location ON places(coords);

-- EXPLAIN / EXPLAIN ANALYZE to see if index is used
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;

-- Index NOT used when:
-- Function wraps the indexed column:  WHERE YEAR(created_at) = 2024  (no index on YEAR)
-- Leading wildcard:                   WHERE name LIKE '%smith'
-- Implicit cast:                      WHERE int_col = '42' (type mismatch)
-- Low selectivity column + small table

-- Index IS used when:
-- Prefix LIKE:       WHERE name LIKE 'smith%'
-- Range on btree:    WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
-- Composite: leftmost columns first — index(a,b,c) helps: a, a+b, a+b+c; not b alone

-- Covering index avoids heap fetch
-- All needed columns are in the index → index-only scan

-- Index on FK columns (always do this — prevents sequential scans on join/delete)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Reindex
REINDEX INDEX idx_users_email;        -- PostgreSQL
REINDEX TABLE users;                  -- PostgreSQL (all indexes on table)
ALTER INDEX idx_users_email REBUILD;  -- SQL Server

-- Basic view
CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';

-- Query a view like a table
SELECT * FROM active_users WHERE created_at > '2024-01-01';

-- View with JOIN
CREATE VIEW order_summary AS
SELECT
    o.id        AS order_id,
    u.name      AS customer,
    o.total,
    o.status,
    o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id;

-- View with aggregation
CREATE VIEW department_stats AS
SELECT
    department,
    COUNT(*)     AS headcount,
    AVG(salary)  AS avg_salary,
    MAX(salary)  AS max_salary
FROM employees
GROUP BY department;

-- OR REPLACE (recreate if exists)
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

-- IF NOT EXISTS (PostgreSQL 9.4+)
CREATE VIEW IF NOT EXISTS active_users AS ...;

-- Drop
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;
DROP VIEW active_users CASCADE;   -- also drops dependent objects

-- Rename (PostgreSQL)
ALTER VIEW active_users RENAME TO verified_users;

-- SQL Server: no rename via ALTER VIEW; must DROP + CREATE

-- This view is updatable
CREATE VIEW active_users AS
SELECT id, name, email, status FROM users WHERE status = 'active';

-- INSERT / UPDATE / DELETE on the view
INSERT INTO active_users (name, email, status) VALUES ('Alice', 'a@example.com', 'active');
UPDATE active_users SET name = 'Bob' WHERE id = 1;
DELETE FROM active_users WHERE id = 1;

-- WITH CHECK OPTION: prevents INSERT/UPDATE that would make the row invisible
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- Now this would error:
UPDATE active_users SET status = 'inactive' WHERE id = 1;  -- ERROR: violates check option

-- LOCAL vs CASCADED check option
CREATE VIEW myview AS SELECT * FROM base_view WHERE col > 10
WITH LOCAL CHECK OPTION;    -- only checks myview's own condition
-- (CASCADED checks all views in the chain)

-- Create materialized view
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at);

-- Query it (reads cached data)
SELECT * FROM monthly_revenue ORDER BY month;

-- Refresh (full re-computation)
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refresh without locking reads (concurrent-safe)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
-- Requires a UNIQUE index on the materialized view:
CREATE UNIQUE INDEX ON monthly_revenue(month);

-- Drop materialized view
DROP MATERIALIZED VIEW monthly_revenue;
DROP MATERIALIZED VIEW IF EXISTS monthly_revenue;

-- Regular view vs materialized view:
-- Regular view:       always fresh; no storage; re-executes query each access
-- Materialized view:  stale until refreshed; stored on disk; fast reads

-- Must use WITH SCHEMABINDING
CREATE VIEW dbo.sales_summary
WITH SCHEMABINDING AS
SELECT
    region,
    COUNT_BIG(*) AS order_count,
    SUM(total)   AS revenue
FROM dbo.orders
GROUP BY region;

-- Create clustered index (materializes the view)
CREATE UNIQUE CLUSTERED INDEX idx_sales_summary ON dbo.sales_summary(region);

-- List all indexes on a table (PostgreSQL)
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- List all indexes (MySQL)
SHOW INDEX FROM users;

-- List views (PostgreSQL)
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'public';

-- Show view definition (PostgreSQL)
SELECT definition FROM pg_views WHERE viewname = 'active_users';

-- Show view definition (MySQL)
SHOW CREATE VIEW active_users;

-- List materialized views (PostgreSQL)
SELECT matviewname, definition FROM pg_matviews;

-- Index size (PostgreSQL)
SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;