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
/**
 * Performance Check Script
 * Checks for missing indexes that could affect LIMIT/OFFSET queries
 */

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

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

echo "🔍 Checking Database Indexes for Performance...\n\n";

// Check existing indexes on music_tracks
echo "📊 Current indexes on music_tracks:\n";
$stmt = $pdo->query("SHOW INDEXES FROM music_tracks");
$indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);

$existing_indexes = [];
foreach ($indexes as $index) {
    $key_name = $index['Key_name'];
    if ($key_name !== 'PRIMARY') {
        if (!isset($existing_indexes[$key_name])) {
            $existing_indexes[$key_name] = [];
        }
        $existing_indexes[$key_name][] = $index['Column_name'];
    }
}

foreach ($existing_indexes as $name => $columns) {
    echo "  - $name: " . implode(', ', $columns) . "\n";
}

echo "\n";

// Check for critical missing indexes
$critical_indexes = [
    'idx_tracks_status_audio' => "CREATE INDEX IF NOT EXISTS idx_tracks_status_audio ON music_tracks(status, audio_url)",
    'idx_tracks_status_created' => "CREATE INDEX IF NOT EXISTS idx_tracks_status_created ON music_tracks(status, created_at)",
    'idx_tracks_user_status_created' => "CREATE INDEX IF NOT EXISTS idx_tracks_user_status_created ON music_tracks(user_id, status, created_at)",
    'idx_tracks_public_status' => "CREATE INDEX IF NOT EXISTS idx_tracks_public_status ON music_tracks(is_public, status, created_at)",
];

$missing = [];
foreach ($critical_indexes as $name => $sql) {
    $index_name = str_replace('CREATE INDEX IF NOT EXISTS ', '', explode(' ON ', $sql)[0]);
    if (!isset($existing_indexes[$index_name])) {
        $missing[$name] = $sql;
        echo "⚠️  MISSING: $index_name\n";
    } else {
        echo "✅ EXISTS: $index_name\n";
    }
}

if (!empty($missing)) {
    echo "\n🔧 Creating missing indexes...\n";
    foreach ($missing as $name => $sql) {
        try {
            $pdo->exec($sql);
            echo "  ✓ Created: $name\n";
        } catch (Exception $e) {
            echo "  ✗ Error creating $name: " . $e->getMessage() . "\n";
        }
    }
} else {
    echo "\n✅ All critical indexes exist!\n";
}

// Test query performance
echo "\n📈 Testing Query Performance...\n";
echo "Testing community_fixed.php style query:\n";

$start = microtime(true);
$stmt = $pdo->prepare("
    SELECT mt.*, u.name as artist_name
    FROM music_tracks mt
    INNER JOIN users u ON mt.user_id = u.id
    WHERE mt.status = 'complete'
    AND (mt.audio_url IS NOT NULL AND mt.audio_url != '')
    AND mt.user_id IS NOT NULL
    AND u.id IS NOT NULL
    AND (mt.is_public = 1 OR mt.is_public IS NULL)
    ORDER BY mt.created_at DESC
    LIMIT ? OFFSET ?
");
$stmt->execute([24, 0]);
$results = $stmt->fetchAll();
$time = (microtime(true) - $start) * 1000;

echo "  Query time: " . round($time, 2) . "ms\n";
echo "  Results: " . count($results) . " tracks\n";

// Explain the query
echo "\n📋 Query Execution Plan:\n";
$explain = $pdo->prepare("
    EXPLAIN SELECT mt.*, u.name as artist_name
    FROM music_tracks mt
    INNER JOIN users u ON mt.user_id = u.id
    WHERE mt.status = 'complete'
    AND (mt.audio_url IS NOT NULL AND mt.audio_url != '')
    AND mt.user_id IS NOT NULL
    AND u.id IS NOT NULL
    AND (mt.is_public = 1 OR mt.is_public IS NULL)
    ORDER BY mt.created_at DESC
    LIMIT 24 OFFSET 0
");
$explain->execute();
$plan = $explain->fetchAll(PDO::FETCH_ASSOC);

foreach ($plan as $row) {
    echo "  Table: {$row['table']}, Type: {$row['type']}, Key: {$row['key']}, Rows: {$row['rows']}\n";
    if ($row['Extra']) {
        echo "    Extra: {$row['Extra']}\n";
    }
}

// Check PDO settings
echo "\n⚙️  PDO Configuration:\n";
$attrs = [
    PDO::ATTR_EMULATE_PREPARES => 'EMULATE_PREPARES',
    PDO::ATTR_ERRMODE => 'ERRMODE',
    PDO::ATTR_DEFAULT_FETCH_MODE => 'DEFAULT_FETCH_MODE'
];

foreach ($attrs as $attr => $name) {
    $value = $pdo->getAttribute($attr);
    echo "  $name: ";
    if (is_bool($value)) {
        echo $value ? 'true' : 'false';
    } else {
        echo $value;
    }
    echo "\n";
}

// Check if EMULATE_PREPARES is false (should be false for real prepared statements)
if ($pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES)) {
    echo "\n⚠️  WARNING: PDO::ATTR_EMULATE_PREPARES is true!\n";
    echo "   This means prepared statements are being emulated in PHP.\n";
    echo "   For better performance and security, set it to false.\n";
} else {
    echo "\n✅ PDO::ATTR_EMULATE_PREPARES is false (using native prepared statements)\n";
}

echo "\n✅ Performance check complete!\n";