-- Single-column index
CREATE INDEX idx_users_email ON users (email);

-- Unique index (also enforces uniqueness constraint)
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Index at table creation time
CREATE TABLE orders (
  id         INT  NOT NULL AUTO_INCREMENT,
  user_id    INT  NOT NULL,
  status     VARCHAR(20),
  created_at DATETIME,
  PRIMARY KEY (id),
  INDEX idx_user_id   (user_id),
  INDEX idx_status    (status),
  INDEX idx_created   (created_at)
);

-- Add an index to an existing table
ALTER TABLE products ADD INDEX idx_category (category_id);
ALTER TABLE products ADD UNIQUE INDEX idx_sku (sku);

-- Show all indexes with cardinality
SHOW INDEX FROM users;
-- Key columns:
--   Cardinality: estimated number of distinct values in the index
--   Non_unique:  0 = UNIQUE, 1 = non-unique
--   Seq_in_index: position in a composite index (1, 2, 3...)

-- Calculate exact selectivity for any column
SELECT
  'email'  AS col, COUNT(DISTINCT email)  / COUNT(*) AS selectivity FROM users
UNION ALL SELECT
  'status',        COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM users;
-- email: ~1.00 (excellent — every email is unique)
-- status: ~0.003 (poor — only 3 distinct values in 1000 rows)

-- Goal: optimize WHERE user_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_composite ON orders (user_id, status, created_at);

-- This composite index is used for all of:
SELECT * FROM orders WHERE user_id = 42;                          -- leftmost prefix
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';     -- first two columns
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'
  ORDER BY created_at;                                            -- all three columns

-- This is NOT used effectively (skips leftmost column):
SELECT * FROM orders WHERE status = 'paid';  -- cannot use the index efficiently

-- Index covers both the WHERE clause and the SELECT list
CREATE INDEX idx_covering ON orders (user_id, status, created_at);

-- All needed columns are in the index — no table access required
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 42;

-- Verify with EXPLAIN
EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id = 42G

-- Index the first 20 characters of email
CREATE INDEX idx_email_prefix ON users (email(20));

-- Find the right prefix length: compare selectivity
SELECT
  COUNT(DISTINCT LEFT(email, 5))  / COUNT(*) AS sel_5,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel_20,
  COUNT(DISTINCT email)           / COUNT(*) AS sel_full
FROM users;

-- Stop adding characters when selectivity plateaus

-- Make an index invisible (optimizer ignores it, but it stays maintained)
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- Run your workload and check performance — if nothing breaks, safe to drop
-- Check query plans changed as expected:
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Restore the index to visible
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

-- Create an invisible index from the start (pre-test before enabling)
CREATE INDEX idx_test ON orders (created_at) INVISIBLE;

-- Suggest an index (optimizer may still ignore it)
SELECT * FROM orders USE INDEX (idx_status)
WHERE status = 'pending';

-- Force the use of a specific index (optimizer must use it)
SELECT * FROM orders FORCE INDEX (idx_status)
WHERE status = 'pending';

-- Tell the optimizer not to use a specific index
SELECT * FROM orders IGNORE INDEX (idx_created_at)
WHERE status = 'pending';

-- MySQL 8.0 optimizer hints (preferred — more expressive)
SELECT /*+ INDEX(orders idx_status) */ *
FROM orders
WHERE status = 'pending';

-- Check fragmentation via information_schema
SELECT TABLE_NAME, DATA_FREE, DATA_LENGTH, INDEX_LENGTH,
       ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 1) AS fragmentation_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders';

-- Rebuild all indexes on a table (reclaims space, rebuilds stats)
OPTIMIZE TABLE orders;

-- Update cardinality statistics only (faster than OPTIMIZE)
ANALYZE TABLE orders;

-- MySQL 8.0: online index rebuild — does not lock reads or writes
ALTER TABLE orders DROP INDEX idx_user_id,
                  ADD INDEX  idx_user_id (user_id),
                  ALGORITHM=INPLACE, LOCK=NONE;

-- MySQL 8.0: indexes with zero usage since server start
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
ORDER BY object_schema, object_name;

-- Find duplicate / redundant indexes with Percona Toolkit
-- pt-duplicate-key-checker --host=localhost --user=root

-- Use sys schema view (MySQL 8.0)
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- List all indexes on a table
SHOW INDEX FROM orders;
SHOW INDEX FROM ordersG  -- vertical output, easier to read

-- Drop an index by name
DROP INDEX idx_status ON orders;

-- Using ALTER TABLE syntax
ALTER TABLE orders DROP INDEX idx_status;

-- Drop primary key (rare — only for tables without AUTO_INCREMENT)
ALTER TABLE legacy_table DROP PRIMARY KEY;

-- Before: no index on email column, 500,000 rows
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com'G
-- type: ALL, rows: 500000, key: NULL

-- After: add a unique index
CREATE UNIQUE INDEX idx_users_email ON users (email);

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com'G
-- type: const, rows: 1, key: idx_users_email

-- The const type means MySQL found at most one matching row via the unique index
-- It is the fastest possible access type

-- A join on an unindexed column is extremely slow on large tables
EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 42G
-- orders table: type: ALL if user_id has no index

-- With an index on orders.user_id:
CREATE INDEX idx_orders_user ON orders (user_id);

EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 42G
-- orders table: type: ref, key: idx_orders_user, rows: 5

-- Rule: always index the column on the many-side of a 1:many JOIN

-- This query CANNOT use a regular index on created_at:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- Because the function wraps the indexed column — optimizer cannot use the index

-- Solution 1 (any MySQL version): rewrite as a range query
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- Now the regular index on created_at works fine

-- Solution 2 (MySQL 8.0+): create a functional index
CREATE INDEX idx_year ON orders ((YEAR(created_at)));
-- Then YEAR(created_at) = 2024 uses this index

-- Cardinality statistics are sampled, not exact
-- After a large bulk insert, the optimizer may have stale estimates
SHOW INDEX FROM orders;
-- Cardinality: 1247  <-- may be outdated after bulk insert

-- Refresh statistics without rebuilding the index
ANALYZE TABLE orders;
-- Result: Table 'orders' Op: analyze Status: OK

-- SHOW INDEX after ANALYZE shows updated cardinality
SHOW INDEX FROM orders;
-- Cardinality: 1823456  <-- now reflects actual data

-- Configure how much data InnoDB samples when computing statistics
-- (higher = more accurate but slower ANALYZE)
SET GLOBAL innodb_stats_persistent_sample_pages = 20;  -- default is 20

-- After adding an index, confirm it is used by your query
-- Step 1: add the index
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Step 2: run EXPLAIN
EXPLAIN SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending'G
-- Confirm: key = idx_orders_user_status, type = ref

-- Step 3: run EXPLAIN ANALYZE (MySQL 8.0) to confirm real performance
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending'G
-- Confirm: actual time is small, actual rows matches rows estimate

-- Step 4: check the slow log after the index is in production
-- The query should no longer appear there