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/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
/**
 * Add Performance Indexes
 * Creates indexes on subquery tables for faster JOINs
 */

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

$pdo = getDBConnection();
if (!$pdo) {
    die("Database connection failed\n");
}

echo "🔧 Adding Performance Indexes...\n\n";

$indexes = [
    // Track statistics indexes (for JOINs)
    "CREATE INDEX IF NOT EXISTS idx_track_likes_track_id ON track_likes(track_id)",
    "CREATE INDEX IF NOT EXISTS idx_track_comments_track_id ON track_comments(track_id)",
    "CREATE INDEX IF NOT EXISTS idx_track_plays_track_id ON track_plays(track_id)",
    "CREATE INDEX IF NOT EXISTS idx_track_shares_track_id ON track_shares(track_id)",
    "CREATE INDEX IF NOT EXISTS idx_track_views_track_id ON track_views(track_id)",
    "CREATE INDEX IF NOT EXISTS idx_track_ratings_track_id ON track_ratings(track_id)",
    "CREATE INDEX IF NOT EXISTS idx_track_votes_track_id ON track_votes(track_id)",
    
    // Composite indexes for user-specific queries
    "CREATE INDEX IF NOT EXISTS idx_track_likes_user_track ON track_likes(user_id, track_id)",
    "CREATE INDEX IF NOT EXISTS idx_track_votes_user_track ON track_votes(user_id, track_id)",
    "CREATE INDEX IF NOT EXISTS idx_user_wishlist_user_track ON user_wishlist(user_id, track_id)",
    
    // Additional composite indexes for better performance
    "CREATE INDEX IF NOT EXISTS idx_track_plays_track_created ON track_plays(track_id, created_at)",
    "CREATE INDEX IF NOT EXISTS idx_track_likes_track_created ON track_likes(track_id, created_at)",
];

$success = 0;
$errors = 0;

foreach ($indexes as $index_sql) {
    try {
        $pdo->exec($index_sql);
        $index_name = preg_match('/INDEX.*?`?(\w+)`?/i', $index_sql, $matches) ? $matches[1] : 'index';
        echo "  ✅ Created: $index_name\n";
        $success++;
    } catch (Exception $e) {
        $error_msg = $e->getMessage();
        // Skip if index already exists
        if (strpos($error_msg, 'Duplicate key name') !== false || strpos($error_msg, 'already exists') !== false) {
            echo "  ⏭️  Already exists: " . substr($index_sql, 0, 60) . "...\n";
        } else {
            echo "  ❌ Error: " . $error_msg . "\n";
            $errors++;
        }
    }
}

echo "\n✅ Index creation complete!\n";
echo "   Success: $success\n";
echo "   Errors: $errors\n";
echo "\n💡 These indexes will significantly speed up JOIN queries!\n";