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

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
/**
 * Deep Performance Analysis
 * Identifies actual performance bottlenecks
 */

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

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

echo "🔍 Deep Performance Analysis\n";
echo str_repeat("=", 80) . "\n\n";

// Enable query logging
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Test the actual queries from community_fixed.php
echo "1️⃣ Testing Community Page Query (community_fixed.php)\n";
echo str_repeat("-", 80) . "\n";

$queries = [];

// Simulate the exact query from community_fixed.php
$start = microtime(true);
$sql = "
    SELECT 
        mt.id,
        mt.title,
        mt.prompt,
        mt.audio_url,
        mt.duration,
        mt.created_at,
        mt.user_id,
        mt.image_url,
        mt.genre,
        mt.tags,
        mt.likes_count,
        mt.plays_count,
        mt.comments_count,
        mt.metadata,
        u.id as artist_id,
        u.name as artist_name,
        u.profile_image,
        COALESCE((SELECT COUNT(*) FROM track_likes WHERE track_id = mt.id), 0) as like_count,
        COALESCE((SELECT COUNT(*) FROM track_comments WHERE track_id = mt.id), 0) as comment_count,
        COALESCE((SELECT AVG(rating) FROM track_ratings WHERE track_id = mt.id), 0) as average_rating,
        COALESCE((SELECT COUNT(*) FROM track_ratings WHERE track_id = mt.id), 0) as rating_count,
        (SELECT vote_type FROM track_votes WHERE track_id = mt.id AND user_id = ? LIMIT 1) as user_vote
    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 != '' OR mt.variations_count > 0)
    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 = $pdo->prepare($sql);
$params = [0, 0, 0, 0, 24, 0]; // user_id (4 times), per_page, offset
$stmt->execute($params);
$results = $stmt->fetchAll();
$time1 = (microtime(true) - $start) * 1000;
$queries[] = ['name' => 'Community query with subqueries', 'time' => $time1, 'rows' => count($results)];

echo "  ⏱️  Time: " . round($time1, 2) . "ms\n";
echo "  📊 Rows: " . count($results) . "\n";

// Check for N+1 problem - count subqueries
$subquery_count = substr_count($sql, 'SELECT');
echo "  🔢 Subqueries in query: " . ($subquery_count - 1) . "\n";

// Test optimized version with JOINs instead of subqueries
echo "\n2️⃣ Testing Optimized Version (with JOINs)\n";
echo str_repeat("-", 80) . "\n";

$start = microtime(true);
$sql_optimized = "
    SELECT 
        mt.id,
        mt.title,
        mt.prompt,
        mt.audio_url,
        mt.duration,
        mt.created_at,
        mt.user_id,
        mt.image_url,
        mt.genre,
        mt.tags,
        mt.likes_count,
        mt.plays_count,
        mt.comments_count,
        mt.metadata,
        u.id as artist_id,
        u.name as artist_name,
        u.profile_image,
        COALESCE(like_stats.like_count, 0) as like_count,
        COALESCE(comment_stats.comment_count, 0) as comment_count,
        COALESCE(rating_stats.avg_rating, 0) as average_rating,
        COALESCE(rating_stats.rating_count, 0) as rating_count,
        0 as user_vote
    FROM music_tracks mt
    INNER JOIN users u ON mt.user_id = u.id
    LEFT JOIN (
        SELECT track_id, COUNT(*) as like_count 
        FROM track_likes 
        GROUP BY track_id
    ) like_stats ON mt.id = like_stats.track_id
    LEFT JOIN (
        SELECT track_id, COUNT(*) as comment_count 
        FROM track_comments 
        GROUP BY track_id
    ) comment_stats ON mt.id = comment_stats.track_id
    LEFT JOIN (
        SELECT track_id, AVG(rating) as avg_rating, COUNT(*) as rating_count
        FROM track_ratings 
        GROUP BY track_id
    ) rating_stats ON mt.id = rating_stats.track_id
    WHERE mt.status = 'complete'
    AND (mt.audio_url IS NOT NULL AND mt.audio_url != '' OR mt.variations_count > 0)
    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 ?
";

$stmt2 = $pdo->prepare($sql_optimized);
$stmt2->execute([24, 0]);
$results2 = $stmt2->fetchAll();
$time2 = (microtime(true) - $start) * 1000;
$queries[] = ['name' => 'Optimized query with JOINs', 'time' => $time2, 'rows' => count($results2)];

echo "  ⏱️  Time: " . round($time2, 2) . "ms\n";
echo "  📊 Rows: " . count($results2) . "\n";
echo "  🚀 Improvement: " . round((($time1 - $time2) / $time1) * 100, 1) . "% faster\n";

// Test library.php query
echo "\n3️⃣ Testing Library Page Query (library.php)\n";
echo str_repeat("-", 80) . "\n";

$start = microtime(true);
$library_sql = "
    SELECT 
        mt.*,
        COALESCE(vars.variation_count, 0) as variation_count,
        CASE 
            WHEN mt.created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN '🔥 Hot'
            WHEN mt.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN '⭐ New'
            ELSE ''
        END as badge
    FROM music_tracks mt
    LEFT JOIN (
        SELECT track_id, COUNT(*) as variation_count 
        FROM audio_variations 
        GROUP BY track_id
    ) vars ON mt.id = vars.track_id
    WHERE mt.user_id = ?
    ORDER BY mt.created_at DESC
    LIMIT ? OFFSET ?
";

$stmt3 = $pdo->prepare($library_sql);
$stmt3->execute([1, 1000, 0]); // user_id, per_page, offset
$results3 = $stmt3->fetchAll();
$time3 = (microtime(true) - $start) * 1000;
$queries[] = ['name' => 'Library query', 'time' => $time3, 'rows' => count($results3)];

echo "  ⏱️  Time: " . round($time3, 2) . "ms\n";
echo "  📊 Rows: " . count($results3) . "\n";

// Check table sizes
echo "\n4️⃣ Database Table Sizes\n";
echo str_repeat("-", 80) . "\n";

$tables = ['music_tracks', 'track_likes', 'track_comments', 'track_ratings', 'users'];
foreach ($tables as $table) {
    try {
        $stmt = $pdo->query("SELECT COUNT(*) as count FROM $table");
        $count = $stmt->fetch()['count'];
        echo "  $table: " . number_format($count) . " rows\n";
    } catch (Exception $e) {
        echo "  $table: Error - " . $e->getMessage() . "\n";
    }
}

// Check for missing indexes on subquery tables
echo "\n5️⃣ Checking Indexes on Subquery Tables\n";
echo str_repeat("-", 80) . "\n";

$subquery_tables = [
    'track_likes' => 'track_id',
    'track_comments' => 'track_id',
    'track_ratings' => 'track_id',
    'track_votes' => 'track_id, user_id'
];

foreach ($subquery_tables as $table => $columns) {
    try {
        $stmt = $pdo->query("SHOW INDEXES FROM $table WHERE Column_name IN ('" . str_replace(', ', "', '", $columns) . "')");
        $indexes = $stmt->fetchAll();
        if (empty($indexes)) {
            echo "  ⚠️  $table: Missing index on ($columns)\n";
        } else {
            $index_names = array_unique(array_column($indexes, 'Key_name'));
            echo "  ✅ $table: Has indexes - " . implode(', ', $index_names) . "\n";
        }
    } catch (Exception $e) {
        echo "  ❌ $table: Error checking indexes\n";
    }
}

// Summary
echo "\n" . str_repeat("=", 80) . "\n";
echo "📊 PERFORMANCE SUMMARY\n";
echo str_repeat("=", 80) . "\n\n";

$total_time = array_sum(array_column($queries, 'time'));
echo "Total query time: " . round($total_time, 2) . "ms\n\n";

foreach ($queries as $q) {
    $percentage = ($q['time'] / $total_time) * 100;
    echo "  {$q['name']}: " . round($q['time'], 2) . "ms (" . round($percentage, 1) . "%)\n";
}

// Recommendations
echo "\n💡 RECOMMENDATIONS\n";
echo str_repeat("-", 80) . "\n";

if ($time1 > 100) {
    echo "⚠️  Community query is slow (>100ms). Consider:\n";
    echo "   - Using the optimized JOIN version instead of subqueries\n";
    echo "   - Adding indexes on track_likes, track_comments, track_ratings\n";
    echo "   - Caching results for 5-10 minutes\n";
}

if ($time3 > 100) {
    echo "⚠️  Library query is slow (>100ms). Consider:\n";
    echo "   - Limiting results per page\n";
    echo "   - Adding pagination\n";
}

echo "\n✅ Analysis complete!\n";