Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory :  /home/gositeme/domains/soundstudiopro.com/private_html/utils/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
// Database Optimization Script
// This script adds indexes to improve query performance

require_once __DIR__ . '/../config/database.php';

$pdo = getDBConnection();

if (!$pdo) {
    echo "Database connection failed.\n";
    exit;
}

$indexes = [
    // Music tracks indexes
    "CREATE INDEX IF NOT EXISTS idx_music_tracks_user_status ON music_tracks(user_id, status)",
    "CREATE INDEX IF NOT EXISTS idx_music_tracks_created_at ON music_tracks(created_at)",
    "CREATE INDEX IF NOT EXISTS idx_music_tracks_status ON music_tracks(status)",
    "CREATE INDEX IF NOT EXISTS idx_music_tracks_task_id ON music_tracks(task_id)",
    
    // User follows indexes
    "CREATE INDEX IF NOT EXISTS idx_user_follows_follower ON user_follows(follower_id)",
    "CREATE INDEX IF NOT EXISTS idx_user_follows_following ON user_follows(following_id)",
    
    // Audio variations indexes
    "CREATE INDEX IF NOT EXISTS idx_audio_variations_track ON audio_variations(track_id)",
    
    // Track likes indexes
    "CREATE INDEX IF NOT EXISTS idx_track_likes_track ON track_likes(track_id)",
    
    // Track plays indexes
    "CREATE INDEX IF NOT EXISTS idx_track_plays_track ON track_plays(track_id)",
    
    // Credit transactions indexes
    "CREATE INDEX IF NOT EXISTS idx_credit_transactions_user ON credit_transactions(user_id, created_at)",
    
    // Email logs indexes
    "CREATE INDEX IF NOT EXISTS idx_email_logs_sent_at ON email_logs(sent_at)",
    "CREATE INDEX IF NOT EXISTS idx_email_logs_user ON email_logs(user_id)",
    
    // Security events indexes
    "CREATE INDEX IF NOT EXISTS idx_security_events_type ON security_events(event_type, created_at)",
    "CREATE INDEX IF NOT EXISTS idx_security_events_user ON security_events(user_id)",
    
    // User login history indexes
    "CREATE INDEX IF NOT EXISTS idx_user_login_history_user ON user_login_history(user_id, created_at)",
    "CREATE INDEX IF NOT EXISTS idx_user_login_history_ip ON user_login_history(ip_address)"
];

$success_count = 0;
$error_count = 0;

foreach ($indexes as $index_sql) {
    try {
        $pdo->exec($index_sql);
        $success_count++;
        echo "✓ " . substr($index_sql, 0, 50) . "...\n";
    } catch (Exception $e) {
        $error_count++;
        echo "✗ Error creating index: " . $e->getMessage() . "\n";
    }
}

echo "\nDatabase optimization complete.\n";
echo "Successfully created: $success_count indexes\n";
echo "Errors: $error_count\n";

// Analyze tables for better query planning
$tables = ['users', 'music_tracks', 'user_follows', 'audio_variations', 'track_likes', 'track_plays', 'credit_transactions', 'email_logs', 'security_events', 'user_login_history'];

foreach ($tables as $table) {
    try {
        $pdo->exec("ANALYZE TABLE $table");
        echo "✓ Analyzed table: $table\n";
    } catch (Exception $e) {
        echo "✗ Error analyzing table $table: " . $e->getMessage() . "\n";
    }
}

echo "\nDatabase analysis complete.\n";
?>