Avoid Over-Indexing #

-- The orders table with the following schema (a real over-indexing situation):
CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NOT NULL,
    tenant_id   VARCHAR(36) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    total       DECIMAL(15,2) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at  TIMESTAMP NULL,

    PRIMARY KEY (id),                                           -- index 1
    INDEX idx_orders_user_id (user_id),                        -- index 2
    INDEX idx_orders_tenant_id (tenant_id),                    -- index 3
    INDEX idx_orders_status (status),                          -- index 4
    INDEX idx_orders_created_at (created_at),                  -- index 5
    INDEX idx_orders_user_status (user_id, status),            -- index 6
    INDEX idx_orders_tenant_status (tenant_id, status),        -- index 7
    INDEX idx_orders_tenant_created (tenant_id, created_at),   -- index 8
    INDEX idx_orders_deleted_at (deleted_at)                   -- index 9
);
-- Total: 9 indexes on one table

-- MySQL: monitor handler statistics to see index write overhead
SHOW GLOBAL STATUS LIKE 'Handler_write';
-- Handler_write increases on every write to a table or index

-- A more detailed way: look at innodb_metrics
SELECT name, count
FROM information_schema.innodb_metrics
WHERE name IN (
    'index_page_writes',
    'index_page_splits',
    'buffer_pool_pages_dirty'
)
ORDER BY name;

-- Before and after dropping unused indexes,
-- compare these values to see the real impact

-- MySQL 8.0+: the sys schema has useful views
-- Check indexes never used since the server restart
SELECT
    t.table_schema,
    t.table_name,
    s.index_name,
    s.column_name,
    s.seq_in_index,
    s.cardinality,
    io.count_star AS times_used
FROM information_schema.statistics s
JOIN information_schema.tables t
    ON t.table_schema = s.table_schema
    AND t.table_name = s.table_name
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage io
    ON io.object_schema = s.table_schema
    AND io.object_name = s.table_name
    AND io.index_name = s.index_name
WHERE t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
  AND io.count_star = 0              -- never used
  AND s.index_name != 'PRIMARY'      -- exclude the primary key
ORDER BY t.table_name, s.index_name;

-- Note: this data resets on every server restart
-- Make sure the server has been running at least 1-2 weeks before auditing
-- to ensure all query patterns are represented

-- Alternative: use sys.schema_unused_indexes (MySQL 5.7+)
SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys')
ORDER BY object_schema, object_name;

-- PostgreSQL: pg_stat_user_indexes stores usage statistics
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,           -- how many times the index was used since tracking began
    idx_tup_read,       -- how many rows were read via the index
    idx_tup_fetch,      -- how many rows were fetched via the index
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0                    -- never used
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;  -- sorted from the largest

-- Unused indexes with large sizes = prime deletion candidates
-- Example alarming output:
-- idx_orders_deleted_at  | 0 scans | 45 MB   ← paying 45MB of storage for 0 benefit
-- idx_orders_status      | 0 scans | 120 MB  ← possibly covered by a composite index

-- A common redundant index example:
-- orders.user_id has two indexes:
INDEX idx_orders_user_id (user_id)                  -- index A
INDEX idx_orders_user_status (user_id, status)       -- index B

-- Index A is redundant because:
-- Every query that can use idx_orders_user_id (WHERE user_id = ?)
-- can also use idx_orders_user_status (which has user_id as a prefix)
-- and is even more optimal because it also covers status
-- The optimizer will almost always choose index B
-- Index A only adds write overhead without read benefit

-- MySQL: find redundant indexes
SELECT
    r.table_schema,
    r.table_name,
    r.index_name AS redundant_index,
    r.column_name AS redundant_columns,
    d.index_name AS dominant_index,
    d.column_name AS dominant_columns
FROM information_schema.statistics r
JOIN information_schema.statistics d
    ON r.table_schema = d.table_schema
    AND r.table_name  = d.table_name
    AND r.index_name  != d.index_name
    AND r.seq_in_index = d.seq_in_index
    AND r.column_name  = d.column_name
WHERE r.seq_in_index = 1  -- compare first columns
  AND r.index_name != 'PRIMARY'
ORDER BY r.table_name, r.index_name;

-- Or use sys.schema_redundant_indexes (MySQL 5.7+):
SELECT *
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys')
ORDER BY table_schema, table_name;

-- PostgreSQL: no built-in view, but you can query pg_index
SELECT
    a.indexname AS index_a,
    b.indexname AS index_b,
    a.tablename
FROM pg_indexes a
JOIN pg_indexes b
    ON a.tablename = b.tablename
    AND a.indexname != b.indexname
    AND a.indexdef != b.indexdef
    -- Index A is redundant if its columns are a prefix of Index B
    AND b.indexdef LIKE '%' || split_part(a.indexdef, '(', 2)
WHERE a.schemaname = 'public'
  AND b.schemaname = 'public';

-- For more precise analysis, use the pg_stat_statements extension
-- and pg_index to inspect indkey (the per-index column array)

-- Pattern 1: a single index whose prefix equals a composite index
INDEX idx_a ON orders (user_id)              -- redundant
INDEX idx_b ON orders (user_id, status)      -- this one is used

-- Pattern 2: a composite index that's a subset of another composite index
INDEX idx_c ON orders (tenant_id, status)    -- redundant
INDEX idx_d ON orders (tenant_id, status, created_at)  -- this one is more complete

-- Pattern 3: an index whose column order is a prefix subset
INDEX idx_e ON orders (user_id, status)      -- still useful if there are queries
                                             -- WHERE user_id = ? AND status = ?
                                             -- without created_at
INDEX idx_f ON orders (user_id, status, created_at)  -- useful for queries
                                                       -- that also ORDER BY created_at

-- Note: Pattern 3 isn't always redundant — depends on the existing queries.
-- Verify with actual queries before dropping

-- Check column selectivity:
SELECT
    COUNT(DISTINCT status) AS unique_values,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT status) / COUNT(*) AS selectivity
FROM orders;

-- Example output:
-- unique_values: 4 (pending, paid, shipped, cancelled)
-- total_rows: 2,000,000
-- selectivity: 0.000002  ← very low

-- An index on the low-selectivity status column:
-- If 'paid' is in 40% of rows (800,000 rows):
-- WHERE status = 'paid' with an index → read 800,000 rows via the index
-- WHERE status = 'paid' without an index → full scan of 2,000,000 rows
-- The optimizer often chooses a full scan because index lookup overhead > full scan
-- for low selectivity!

-- Rule of thumb: a single index on a column with < 5% unique values
-- is almost never useful unless combined in a composite index
SELECT
    column_name,
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM information_schema.columns c
-- this query is pseudo-code; the actual implementation needs dynamic SQL per column

-- Step 1: make the index invisible (the optimizer won't use it, but it's still updated)
ALTER TABLE orders ALTER INDEX idx_orders_status INVISIBLE;

-- Step 2: monitor for 1-2 days
-- If no query regresses → the index truly isn't needed

-- Step 3: if safe, permanently drop
DROP INDEX idx_orders_status ON orders;

-- Step 3 alternative: if there are problems, restore visibility
ALTER TABLE orders ALTER INDEX idx_orders_status VISIBLE;

-- This feature is very useful because:
-- - The index stays up to date (no rebuild needed if restored)
-- - The write performance impact can be tested first
-- - Instant rollback if problems arise

-- Initial condition: 9 indexes on the orders table
-- After 3 weeks of monitoring, usage data:

-- sys.schema_unused_indexes shows:
-- idx_orders_user_id    → 0 times used
-- idx_orders_tenant_id  → 0 times used
-- idx_orders_status     → 0 times used
-- idx_orders_created_at → 3 times used (monthly report query)
-- idx_orders_deleted_at → 0 times used

-- sys.schema_redundant_indexes shows:
-- idx_orders_user_id   → redundant, covered by idx_orders_user_status
-- idx_orders_tenant_id → redundant, covered by idx_orders_tenant_status

-- Further analysis for the unused ones:
-- idx_orders_status: all queries use composite indexes
--   (tenant_id, status) or (user_id, status)
-- idx_orders_deleted_at: all queries filtering deleted_at IS NULL
--   use other composite indexes that also cover this condition

-- Decision:
-- Drop: idx_orders_user_id (redundant)
-- Drop: idx_orders_tenant_id (redundant)
-- Drop: idx_orders_status (unused, covered by composites)
-- Drop: idx_orders_deleted_at (unused)
-- Keep: idx_orders_created_at (used by the monthly report)
-- Keep: idx_orders_user_status (used by the main query)
-- Keep: idx_orders_tenant_status (used by the main query)
-- Keep: idx_orders_tenant_created (used by the listing query)

-- From 9 indexes → 5 indexes (dropping 4 useless ones)

-- Process with invisible indexes first:
ALTER TABLE orders ALTER INDEX idx_orders_user_id INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_orders_tenant_id INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_orders_status INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_orders_deleted_at INVISIBLE;
-- Monitor for 2 days...
-- No regression → permanently drop:
DROP INDEX idx_orders_user_id ON orders;
DROP INDEX idx_orders_tenant_id ON orders;
DROP INDEX idx_orders_status ON orders;
DROP INDEX idx_orders_deleted_at ON orders;

-- ✗ Anti-pattern 1: indexing every column "to be safe"
CREATE INDEX idx_col1 ON orders (user_id);
CREATE INDEX idx_col2 ON orders (status);
CREATE INDEX idx_col3 ON orders (created_at);
CREATE INDEX idx_col4 ON orders (updated_at);
CREATE INDEX idx_col5 ON orders (tenant_id);
-- ✓ Solution: analyze real queries, create the right composite indexes

-- ✗ Anti-pattern 2: indexes before queries exist
-- "There will surely be a query needing this later"
CREATE INDEX idx_orders_meta ON orders (metadata_type, source_system);
-- These columns have never appeared in any WHERE query
-- ✓ Solution: wait until there's a real need

-- ✗ Anti-pattern 3: never auditing indexes
-- A table with 12 indexes, 5 of which are never used
-- ✓ Solution: audit periodically every 3-6 months

-- ✗ Anti-pattern 4: dropping indexes without a monitoring period
DROP INDEX idx_orders_status ON orders;
-- Dropped directly without impact testing → some rare query
-- may suddenly become slow
-- ✓ Solution: invisible index first, monitor, then drop

-- ✗ Anti-pattern 5: not accounting for periodic queries
-- idx_orders_created_at isn't used daily,
-- but is used by the monthly report
-- A 3-day audit → "unused" → dropped → the monthly report crashes
-- ✓ Solution: an audit period of at least 4-6 weeks to catch all patterns