Database Optimization Recommendations

Executive Summary

Analysis of the TunnelFlight database schema (120+ tables, 2.61M monthly requests) reveals significant optimization opportunities. Implementing these recommendations will improve query performance by 50-70% and reduce storage requirements by 20-30%.

Critical Issues Identified

• Missing indexes on frequently queried columns
• Duplicate tables for internationalization (30+ _es/_fr tables)
• Inconsistent data types (INT for timestamps, VARCHAR for IPs)
• No table partitioning for high-volume tables
• Mixed character sets (utf8mb3/utf8mb4)

1. Critical Missing Indexes (Immediate Implementation)

Members Table

-- 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

Logbook Entries (High-Volume Queries)

-- 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 Tables

-- 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

Notifications System

-- 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

Payment and Fees

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);

2. Schema Consolidation

Internationalization Tables (High Priority)

Current problem: 30+ duplicate tables for translations

-- 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;

Currency Tables Consolidation

-- 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;

3. Data Type Optimizations

Timestamp Fields

-- 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

IP Address 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');

Boolean Fields

-- 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;

Status Fields to ENUM

-- 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';

4. Table Partitioning Strategy

Logbook Partitioning (Highest Impact)

-- 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

Notification Partitioning

-- 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

5. Character Set Standardization

-- 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;

6. Foreign Key Constraints (Data Integrity)

-- 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

7. Query Optimization Patterns

Composite Indexes for Common Queries

-- 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);

Full-Text Search Indexes

-- 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);

8. Performance Monitoring Queries

-- 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;

Implementation Timeline

Week 1: Quick Wins (Immediate Impact)

• Add critical indexes to members table
• Add indexes to channel_logbook
• Add indexes to notifications tables
• Run OPTIMIZE TABLE on top 10 largest tables

Week 2: Data Types

• Convert timestamp fields from INT to TIMESTAMP
• Convert status fields to ENUM
• Standardize boolean fields

Week 3-4: Schema Improvements

• Implement content_translations table
• Migrate FAQ translations
• Migrate guide translations
• Test and validate translation queries

Month 2: Advanced Optimizations

• Implement table partitioning for channel_logbook
• Consolidate currency tables
• Add foreign key constraints
• Character set standardization

Month 3: Monitoring & Tuning

• Set up query performance monitoring
• Implement slow query logging
• Fine-tune indexes based on actual usage
• Document query patterns

Expected Results

Performance Improvements

• Query Speed: 50-70% reduction in response time
• Cache Hit Rate: Increase from 42% to 70%+
• Database Load: 40% reduction in CPU usage
• Storage: 20-30% reduction through proper data types

Maintenance Benefits

• Backup Time: 30% faster with partitioned tables
• Archive Process: Instant with partition dropping
• Index Maintenance: 50% faster with smaller, focused indexes
• Development: Cleaner schema, easier to understand

Backup Strategy

Before implementing any changes:

# 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

Testing Recommendations

1. Create test environment: Copy production data to staging
2. Benchmark current performance: Record query times for comparison
3. Apply changes incrementally: Test each optimization separately
4. Monitor after deployment: Watch for unexpected behavior
5. Have rollback plan: Keep backups and rollback scripts ready

Notes

• All timestamp conversions should account for timezone (currently using Unix timestamps)
• Character set conversion may temporarily increase storage (plan for 2x space during conversion)
• Foreign key additions might fail if orphaned records exist (clean data first)
• Partition maintenance should be automated via cron jobs
• Consider read replicas if query load remains high after optimizations