-- Primary key automatically creates an index
CREATE TABLE users (
  id INT AUTO_INCREMENT,
  email VARCHAR(255),
  PRIMARY KEY (id)
);

ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- Single-column index
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- Improves queries like: SELECT * FROM orders WHERE user_id = 123;

ALTER TABLE orders ADD INDEX idx_user_date (user_id, created_at);

-- Optimizes: SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

ALTER TABLE articles ADD FULLTEXT INDEX idx_content (title, body);

-- Search query
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database indexing');

SHOW INDEXES FROM users \G

-- Look for the Cardinality column
-- Higher cardinality = more selective index = better performance

SELECT id, clmn_a, clmn_b, clmn_c
FROM tbl_name
WHERE clmn_a = 234
AND clmn_b = 1234
ORDER BY clmn_c DESC;

-- ❌ BAD: Inefficient sorting by ID when better options exist
SELECT * FROM orders WHERE user_id = 123 ORDER BY id DESC;

-- ✅ GOOD: Index on (user_id, created_at)
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);

-- Rewrite query
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- Review slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Then create indexes only for slow queries

-- ❌ DON'T index status (only 5 unique values)
ALTER TABLE orders ADD INDEX idx_status (status);

gh-ost \
  --user=<user> \
  --password=<password> \
  --host=<host> \
  --database=<database> \
  --table=<table_name> \
  --alter="ADD INDEX idx_new_column (new_column)" \
  --execute

-- Set up test data
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50),
  price DECIMAL(10, 2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert test records (adjust the number as needed)
INSERT INTO products (name, category, price) VALUES
('Laptop', 'Electronics', 899.99),
('Mouse', 'Electronics', 29.99),
('Keyboard', 'Electronics', 79.99),
('Desk Chair', 'Furniture', 299.99),
('Monitor', 'Electronics', 449.99);

-- Query WITHOUT index - full table scan
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

-- Create index
ALTER TABLE products ADD INDEX idx_category (category);

-- Query WITH index - index scan (much faster!)
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

-- Create test table
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  product_id INT,
  quantity INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO orders (user_id, product_id, quantity) VALUES
(101, 1, 2),
(102, 2, 1),
(101, 3, 5),
(103, 1, 1);

-- Query that benefits from composite index
SELECT * FROM orders WHERE user_id = 101 AND created_at > '2024-01-01' ORDER BY created_at DESC;

-- Create composite index (high to low cardinality)
ALTER TABLE orders ADD INDEX idx_user_date (user_id, created_at DESC);

-- Now run EXPLAIN to verify index usage
EXPLAIN SELECT * FROM orders WHERE user_id = 101 AND created_at > '2024-01-01' ORDER BY created_at DESC;

# Enable slow query log (run on MySQL server)
mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL long_query_time = 1;  # Log queries taking > 1 second
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';

# Tail the slow query log
tail -f /var/log/mysql/slow.log

# Analyze with pt-query-digest
pt-query-digest /var/log/mysql/slow.log | head -20

-- Find unused indexes (in MySQL 5.7+)
SELECT object_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema != 'mysql'
ORDER BY count_star DESC;

-- Drop unused indexes to reduce write overhead
-- ALTER TABLE <table> DROP INDEX <unused_index>;

CREATE TABLE test1 (
  id INTEGER,
  content VARCHAR
);

-- Standard index
CREATE INDEX test1_id_index ON test1 (id);

-- Remove index
DROP INDEX test1_id_index;

-- ❌ This locks the table against writes
CREATE INDEX idx_new ON large_table (column);

-- Other transactions will BLOCK if they try to INSERT/UPDATE/DELETE

-- ✅ RECOMMENDED for production
CREATE INDEX CONCURRENTLY idx_new ON large_table (column);

-- Other transactions can continue reading/writing
-- BUT: Requires 2 table scans + more CPU/IO
-- AND: Takes significantly longer than standard index creation

-- Only B-Tree indexes can be declared unique
CREATE UNIQUE INDEX name ON table (column [, ...]);

-- Key properties:
-- - Multiple rows with equal values not allowed
-- - NULL values are NOT considered equal to each other
-- - Multicolumn unique indexes reject only when ALL columns match

EXPLAIN SELECT * FROM tenk1;

-- QUERY PLAN
-- -------------------------------------------------------------
-- Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

-- Create demo table
CREATE TABLE demotable (num NUMERIC, id INT);
CREATE INDEX demoidx ON demotable(num);

-- Insert 1M rows
INSERT INTO demotable 
  SELECT random() * 1000, generate_series(1, 1000000);

ANALYZE;  -- Update table statistics

-- Query that triggers sequential scan
EXPLAIN SELECT * FROM demotable WHERE num < 21000;

-- QUERY PLAN
-- -----------------------------------------------------------------------
-- Seq Scan on demotable  (cost=0.00..17989.00 rows=1000000 width=15)
--   Filter: (num < '21000'::numeric)

-- Query returns few rows (selective)
EXPLAIN SELECT * FROM demotable WHERE num = 21000;

-- QUERY PLAN
-- -----------------------------------------------------------------------
-- Index Scan using demoidx on demotable  (cost=0.42..8.44 rows=1 width=15)
--   Index Cond: (num = '21000'::numeric)

-- Query fetches ONLY indexed columns
EXPLAIN SELECT num FROM demotable WHERE num = 21000;

-- QUERY PLAN
-- -----------------------------------------------------------------------
-- Index Only Scan using demoidx on demotable  (cost=0.42..8.44 rows=1 width=11)
--   Index Cond: (num = '21000'::numeric)

-- Query returns moderate number of rows (not too few, not too many)
EXPLAIN SELECT * FROM demotable WHERE num < 210;

-- QUERY PLAN
-- -----------------------------------------------------------------------
-- Bitmap Heap Scan on demotable  (cost=5883.50..14035.53 rows=213042 width=15)
--   Recheck Cond: (num < '210'::numeric)
--   -> Bitmap Index Scan on demoidx  (cost=0.00..5830.24 rows=213042 width=0)
--        Index Cond: (num < '210'::numeric)

-- Fetches ALL columns → Bitmap Scan (needs heap)
EXPLAIN SELECT * FROM demotable WHERE num < 210;
-- Result: Bitmap Heap Scan

-- Fetches ONLY indexed column → Index-Only Scan (no heap needed)
EXPLAIN SELECT num FROM demotable WHERE num < 210;
-- Result: Index Only Scan

| id | select_type | table | type | possible_keys | key | rows | filtered | Extra |
|  1 | SIMPLE      | users | ref  | idx_email     | idx | 1    | 100%     | NULL  |

Seq Scan on users
  Filter: (email = 'test@example.com')
  (cost=0.00..35.50 rows=1 width=100)

-- Create demo table
CREATE TABLE person (
  name TEXT,
  salary INT,
  age INT
);

-- Generate 10 million rows
INSERT INTO person (name, salary, age)
SELECT 
  UPPER(SUBSTR(MD5(RANDOM()::TEXT), 1, 6)),
  FLOOR(RANDOM() * (1000 - 100 + 1)) + 100,
  FLOOR(RANDOM() * (60 - 20 + 1)) + 20
FROM GENERATE_SERIES(1, 10000000);

-- Update table statistics
ANALYZE person;

-- Check table size
SELECT
  ROUND(PG_TOTAL_RELATION_SIZE('person') / 1024 / 1024, 2) AS size_mb;

-- Query without index
EXPLAIN SELECT * FROM person WHERE age > 50;

-- Expected: Seq Scan (scans all 10M rows)

-- Create index
CREATE INDEX idx_age ON person (age);

-- Query with index
EXPLAIN SELECT * FROM person WHERE age > 50;

-- Expected: Seq Scan or Bitmap Scan (depending on row percentage)
-- If < 5% of rows: Index Scan
-- If > 10% of rows: Seq Scan
-- If 5-10%: Bitmap Scan

-- Standard (table locked during creation):
-- CREATE INDEX idx_salary ON person (salary);

-- Production-safe (allows concurrent writes):
CREATE INDEX CONCURRENTLY idx_salary ON person (salary);

-- Takes longer and more CPU/IO, but table remains usable

-- Index Scan (very selective, small result set)
EXPLAIN SELECT * FROM person WHERE age = 45;
-- Result: Index Scan

-- Bitmap Scan (moderately selective, medium result set)
EXPLAIN SELECT * FROM person WHERE age > 40 AND age < 60;
-- Result: Bitmap Scan

-- Sequential Scan (low selectivity, large result set)
EXPLAIN SELECT * FROM person WHERE age > 20;
-- Result: Seq Scan