Query Optimization in MySQL

-- Explain query
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 'pending';

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Query profiling
SET profiling = 1;
SELECT * FROM large_table WHERE status = 'active';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- DETAILED_TAB:
-- Optimizer hints (MySQL 8.0+)
SELECT /*+ INDEX(users idx_email) */ *
FROM users WHERE email = 'john@example.com';

-- Analyze table statistics
ANALYZE TABLE users;
OPTIMIZE TABLE users;  -- Defragment

-- Query cache (removed in 8.0 - variables no longer exist)
SHOW VARIABLES LIKE 'query_cache%';

-- Performance schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Find tables without primary key
SELECT tables.table_schema, tables.table_name
FROM information_schema.tables
LEFT JOIN information_schema.key_column_usage AS c
ON tables.table_name = c.table_name
  AND c.constraint_name = 'PRIMARY'
WHERE tables.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
  AND c.constraint_name IS NULL;