Database Optimization Recommendations

-- Core member lookups (used in authentication and every API call)
ALTER TABLE members 
ADD INDEX idx_email (email),
ADD INDEX idx_verified_email (verified_email),
ADD INDEX idx_role_last_visit (role_id, last_visit),
ADD INDEX idx_tunnel (tunnel),
ADD INDEX idx_country (country);
 
-- Expected impact: 60% faster member lookups

-- Most common query patterns for logbook
ALTER TABLE channel_logbook
ADD INDEX idx_member_entry_date (member, entry_date),
ADD INDEX idx_status (status),
ADD INDEX idx_tunnel_date (tunnel, entry_date),
ADD INDEX idx_member_tunnel_status (member, tunnel, status);
 
-- Expected impact: 70% faster logbook queries

-- Currency validation checks
ALTER TABLE channel_currency_checkboxes
ADD INDEX idx_entry_instructor (entry_id, instructor),
ADD INDEX idx_passed_date (passed, entry_date),
ADD INDEX idx_instructor_passed (instructor, passed);
 
ALTER TABLE channel_currency_flyer_checkboxes
ADD INDEX idx_entry_instructor (entry_id, instructor),
ADD INDEX idx_level_approval (level, approval_level),
ADD INDEX idx_passed_date (passed, entry_date);
 
-- Repeat for other currency tables
-- Expected impact: 50% faster currency checks

-- Real-time notification queries
ALTER TABLE channel_notifications
ADD INDEX idx_member_status_date (member_id, status, entry_date),
ADD INDEX idx_status_date (status, entry_date);
 
ALTER TABLE channel_notifications_read
ADD INDEX idx_member_notification (member_id, notification_id),
ADD INDEX idx_notification_read_date (notification_id, read_date);
 
-- Expected impact: 80% faster notification retrieval

ALTER TABLE fees
ADD INDEX idx_member_date (member_id, date),
ADD INDEX idx_status (status),
ADD INDEX idx_stripe_session (stripe_session_id);
 
ALTER TABLE costs
ADD INDEX idx_code (code),
ADD INDEX idx_category (category);

-- Create unified translation table
CREATE TABLE content_translations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  entity_type VARCHAR(50) NOT NULL, -- 'faq', 'guide', 'news', 'skill'
  entity_id INT NOT NULL,
  language VARCHAR(5) NOT NULL DEFAULT 'en',
  title VARCHAR(255),
  content TEXT,
  url_title VARCHAR(128),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_entity (entity_type, entity_id, language),
  INDEX idx_language (language),
  FULLTEXT idx_search (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
-- Migration example for FAQs
INSERT INTO content_translations (entity_type, entity_id, language, title, content, url_title)
SELECT 'faq', entry_id, 'en', title, answer, url_title FROM channel_faqs
UNION ALL
SELECT 'faq', entry_id, 'es', title, answer, url_title FROM channel_faqs_es
UNION ALL
SELECT 'faq', entry_id, 'fr', title, answer, url_title FROM channel_faqs_fr;

-- Consolidate 5 currency tables into one
CREATE TABLE currency_records (
  id INT PRIMARY KEY AUTO_INCREMENT,
  member_id INT NOT NULL,
  currency_type ENUM('instructor','trainer','flyer','coach','military') NOT NULL,
  label VARCHAR(256),
  instructor_id INT,
  tunnel_id INT,
  passed BOOLEAN DEFAULT FALSE,
  level INT DEFAULT NULL,
  approval_level INT DEFAULT NULL,
  time INT DEFAULT NULL,
  entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_new BOOLEAN DEFAULT FALSE,
  INDEX idx_member_type (member_id, currency_type),
  INDEX idx_instructor (instructor_id),
  INDEX idx_tunnel (tunnel_id),
  INDEX idx_passed (passed),
  INDEX idx_date (entry_date),
  FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE,
  FOREIGN KEY (instructor_id) REFERENCES members(member_id) ON DELETE SET NULL,
  FOREIGN KEY (tunnel_id) REFERENCES channel_tunnels(entry_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Convert INT timestamps to TIMESTAMP type
ALTER TABLE members 
MODIFY join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MODIFY last_visit TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
ADD INDEX idx_last_visit (last_visit);
 
ALTER TABLE channel_logbook
MODIFY entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MODIFY edit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
 
-- Benefits: Native date functions, automatic timezone handling, smaller storage

-- Convert VARCHAR(45) to VARBINARY(16) for IPv4/IPv6
ALTER TABLE members 
ADD COLUMN ip_address_bin VARBINARY(16),
ADD INDEX idx_ip (ip_address_bin);
 
-- Migration
UPDATE members SET ip_address_bin = INET6_ATON(ip_address);
ALTER TABLE members DROP COLUMN ip_address;
ALTER TABLE members RENAME COLUMN ip_address_bin TO ip_address;
 
-- Query usage
SELECT * FROM members WHERE ip_address = INET6_ATON('192.168.1.1');

-- Convert TINYINT(1) to BOOLEAN
ALTER TABLE members
MODIFY is_coach BOOLEAN DEFAULT FALSE,
MODIFY is_military BOOLEAN DEFAULT FALSE,
MODIFY currency_instructor BOOLEAN DEFAULT TRUE,
MODIFY currency_trainer BOOLEAN DEFAULT FALSE,
MODIFY currency_flyer BOOLEAN DEFAULT TRUE,
MODIFY currency_coach BOOLEAN DEFAULT TRUE,
MODIFY currency_military BOOLEAN DEFAULT TRUE,
MODIFY verified_email BOOLEAN DEFAULT FALSE,
MODIFY verified_phone BOOLEAN DEFAULT FALSE;

-- Convert VARCHAR status fields to ENUM
ALTER TABLE channel_logbook
MODIFY status ENUM('pending', 'approved', 'rejected', 'expired', 'deleted') DEFAULT 'pending';
 
ALTER TABLE channel_change_requests
MODIFY status ENUM('pending', 'approved', 'rejected', 'processing') DEFAULT 'pending';
 
ALTER TABLE fees
MODIFY status ENUM('pending', 'paid', 'failed', 'refunded', 'cancelled') DEFAULT 'pending';

-- Partition by year for historical data management
ALTER TABLE channel_logbook
PARTITION BY RANGE (YEAR(entry_date)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pfuture VALUES LESS THAN MAXVALUE
);
 
-- Benefits: 3-4x faster date-range queries, easy archival

-- Partition by month for recent data access
ALTER TABLE channel_notifications
PARTITION BY RANGE (TO_DAYS(entry_date)) (
  PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  -- Continue for 12 months
  PARTITION pfuture VALUES LESS THAN MAXVALUE
);
 
-- Auto-drop old partitions
ALTER TABLE channel_notifications DROP PARTITION p202501; -- After 6 months

-- Convert all tables to utf8mb4 for full Unicode support
ALTER DATABASE tunnelflight CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
-- Convert each table
ALTER TABLE members CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE channel_logbook CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Continue for all tables
 
-- Update connection settings in application
-- SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Add missing foreign keys for referential integrity
ALTER TABLE channel_logbook
ADD CONSTRAINT fk_logbook_member 
  FOREIGN KEY (member) REFERENCES members(member_id) ON DELETE CASCADE,
ADD CONSTRAINT fk_logbook_tunnel 
  FOREIGN KEY (tunnel) REFERENCES channel_tunnels(entry_id) ON DELETE SET NULL,
ADD CONSTRAINT fk_logbook_instructor 
  FOREIGN KEY (instructor) REFERENCES members(member_id) ON DELETE SET NULL;
 
ALTER TABLE channel_change_requests
ADD CONSTRAINT fk_change_member 
  FOREIGN KEY (member) REFERENCES members(member_id) ON DELETE CASCADE,
ADD CONSTRAINT fk_change_author 
  FOREIGN KEY (author_id) REFERENCES members(member_id) ON DELETE SET NULL;
 
-- Benefits: Prevents orphaned records, cascading deletes

-- Member directory search
ALTER TABLE members
ADD INDEX idx_search (role_id, country, tunnel, verified_email, last_visit);
 
-- Logbook filtering
ALTER TABLE channel_logbook
ADD INDEX idx_filter (member, status, tunnel, entry_date);
 
-- Skills lookup
ALTER TABLE channel_skills
ADD INDEX idx_skill_lookup (member_id, skill, passed, date);

-- Content search optimization
ALTER TABLE channel_faqs ADD FULLTEXT(title, answer);
ALTER TABLE channel_news ADD FULLTEXT(title, content);
ALTER TABLE channel_reference_materials ADD FULLTEXT(title, description);
 
-- Usage: SELECT * FROM channel_faqs WHERE MATCH(title, answer) AGAINST('safety training' IN BOOLEAN MODE);

-- Find missing indexes
SELECT 
  tables.table_name,
  statistics.column_name,
  statistics.cardinality
FROM information_schema.tables
LEFT JOIN information_schema.statistics 
  ON tables.table_name = statistics.table_name
WHERE tables.table_schema = 'tunnelflight'
  AND statistics.index_name IS NULL
  AND tables.table_rows > 1000;
 
-- Find unused indexes
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 = 'tunnelflight';
 
-- Table size analysis
SELECT 
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
  table_rows AS 'Row Count'
FROM information_schema.tables
WHERE table_schema = 'tunnelflight'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

# Full backup
mysqldump -u root -p tunnelflight > tunnelflight_backup_$(date +%Y%m%d).sql
 
# Table-specific backup before major changes
mysqldump -u root -p tunnelflight members > members_backup_$(date +%Y%m%d).sql