Priya cuts the database bill by 60 percent

-- Table sizes:
SELECT
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb,
    table_rows
FROM information_schema.TABLES
WHERE table_schema = 'myapp_db'
ORDER BY size_mb DESC;

-- data_free is wasted space from deletes -- reclaim it:
OPTIMIZE TABLE orders;   -- reclaims fragmented space (locks the table briefly)

-- Check index sizes:
SELECT
    table_name, index_name,
    ROUND(stat_value * 16 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'myapp_db'
AND stat_name = 'size'
ORDER BY size_mb DESC;

-- Indexes that MySQL has never used:
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 = 'myapp_db'
ORDER BY object_name, index_name;

-- DROP indexes that are never used -- they only slow down writes

-- Prepared statements are more efficient for repeated queries:
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 42;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

-- In application code (Python example):
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
-- The database engine caches the query plan for prepared statements

-- Check current connections:
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- In your app, use a connection pool:
-- Python: use SQLAlchemy's pool_size=10, max_overflow=20
-- Node.js: use mysql2 with connectionLimit: 10
-- Java: use HikariCP with maximumPoolSize: 10

-- Right-size max_connections in my.cnf:
-- Formula: (RAM_for_connections) / (RAM_per_connection)
-- Each MySQL connection uses about 4-16MB
-- With 8GB RAM for connections: max ~500 connections

-- Instead of hitting MySQL 1000 times:
SELECT city, COUNT(*) FROM users GROUP BY city;

-- Hit MySQL once, cache in Redis for 60 seconds:
-- Python example:
-- cache_key = 'users_by_city'
-- cached = redis.get(cache_key)
-- if not cached:
--     result = cursor.execute("SELECT city, COUNT(*) FROM users GROUP BY city")
--     redis.setex(cache_key, 60, json.dumps(result))

-- Which queries to cache:
-- Dashboard counts and aggregates
-- Lookup tables that rarely change (cities, categories, product types)
-- Queries that are slow AND called frequently

[mysqld]
# Most important: 70-80% of RAM for InnoDB buffer pool
innodb_buffer_pool_size = 12G       # on a 16GB server
innodb_buffer_pool_instances = 8    # one per GB of buffer pool, max 8

# Reduce disk I/O (set to 2 for safety, 0 for speed):
innodb_flush_log_at_trx_commit = 1  # 1=safe, 2=faster

# Log file size (larger = faster writes, longer recovery):
innodb_log_file_size = 256M

# Connection limits:
max_connections = 200
wait_timeout = 600       # close idle connections after 10 minutes
interactive_timeout = 600

# Query cache (MySQL 8 removed it -- use Redis instead):
slow_query_log = 1
long_query_time = 0.5     # log queries over 0.5 seconds