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 ]

# Homepage Performance Audit Report
**Date:** 2025-01-27  
**Issue:** Homepage taking 30+ seconds to load

## Critical Issues Found & Fixed

### 1. ⚠️ **BLOCKING EXTERNAL API CALL** (CRITICAL - FIXED)
**Location:** `includes/security_tracking.php` - `getGeoLocation()` function

**Problem:**
- Synchronous `file_get_contents()` call to `ip-api.com` with NO timeout
- This blocks the entire page load if the external API is slow (5-30+ seconds)
- Called on EVERY page load for geo-location tracking

**Fix Applied:**
- Replaced with cURL with 1-second timeout
- Non-blocking - returns defaults immediately if API is slow
- Prevents homepage from hanging on external API delays

**Impact:** Should reduce load time by 5-30 seconds in worst-case scenarios

---

### 2. ⚠️ **N+1 QUERY PROBLEMS** (CRITICAL - FIXED)

#### A. `api/get_vip_sample_tracks.php`
**Problem:**
- Correlated subqueries: `(SELECT COUNT(*) FROM track_plays WHERE track_id = mt.id)` for EACH track
- Loading 50 tracks = 100+ additional COUNT queries
- Each query scans entire `track_plays` and `track_likes` tables

**Fix Applied:**
- Replaced with LEFT JOINs using pre-aggregated subqueries
- Now: 1 query with aggregated JOINs
- **Performance gain:** ~100x faster for 50 tracks

#### B. `api/get_featured_tracks.php`
**Problem:** Same N+1 issue as above
**Fix Applied:** Same optimization with LEFT JOINs

#### C. `api/get_community_playlists.php`
**Problem:**
- Correlated subqueries for `total_likes` and `total_plays` per playlist
- Nested subqueries: `SELECT track_id FROM playlist_tracks WHERE playlist_id = ap.id`

**Fix Applied:**
- Replaced with LEFT JOINs using pre-aggregated statistics
- **Performance gain:** Significant improvement for playlist listings

---

### 3. ⚠️ **HEADER NOTIFICATION QUERIES** (OPTIMIZED)
**Location:** `includes/header.php` lines 2468-2475

**Problem:**
- Multiple JOIN queries with COUNT for notifications
- Runs on every page load for logged-in users

**Fix Applied:**
- Improved query structure (though these are already relatively efficient)
- Consider caching notification counts in session

---

## Remaining Optimization Opportunities

### 4. 🔄 **DUPLICATE PLAYLIST LOADING**
**Location:** `index.php` and `global_player.php`

**Issue:**
- `loadPlaylist('vip')` called multiple times:
  - `index.php` line 3388: DOMContentLoaded
  - `index.php` line 3413: window.load with 500ms delay
  - `global_player.php` line 1305: setTimeout 1000ms delay

**Recommendation:**
- Consolidate to single initialization point
- Remove redundant calls to prevent duplicate API requests

---

### 5. 📊 **DATABASE INDEXES** (RECOMMENDED)
Ensure these indexes exist for optimal performance:

```sql
-- Critical indexes for track queries
CREATE INDEX idx_tracks_status_audio ON music_tracks(status, audio_url);
CREATE INDEX idx_tracks_vip ON music_tracks(is_vip_sample, status, playlist_order);
CREATE INDEX idx_tracks_featured ON music_tracks(is_featured, status, playlist_order);
CREATE INDEX idx_tracks_user_status ON music_tracks(user_id, status);

-- Critical indexes for statistics
CREATE INDEX idx_track_plays_track ON track_plays(track_id);
CREATE INDEX idx_track_likes_track ON track_likes(track_id);
CREATE INDEX idx_track_comments_track ON track_comments(track_id);

-- Indexes for playlist queries
CREATE INDEX idx_playlist_tracks_playlist ON playlist_tracks(playlist_id, track_id);
```

---

### 6. 🚀 **CACHING OPPORTUNITIES**

#### A. API Response Caching
**Files:** `api/get_vip_sample_tracks.php`, `api/get_featured_tracks.php`

**Recommendation:**
- Cache API responses for 5-10 minutes
- Use Redis or file-based caching
- Invalidate on track updates

#### B. Notification Count Caching
**Location:** `includes/header.php`

**Recommendation:**
- Cache notification counts in session (refresh every 30 seconds)
- Reduces database queries on every page load

---

### 7. 📦 **JAVASCRIPT LOADING**

**Current State:**
- Multiple `DOMContentLoaded` listeners
- Multiple `window.load` listeners
- Redundant initialization code

**Recommendation:**
- Consolidate initialization
- Use async/defer for non-critical scripts
- Lazy load global player if not immediately needed

---

### 8. 🔍 **SECURITY TRACKING OPTIMIZATION**

**Location:** `includes/security_tracking.php`

**Current:**
- Geo-location API call on every page (now optimized with timeout)
- Page visit logging (currently disabled)

**Recommendation:**
- Consider async logging (queue system)
- Batch log writes instead of individual inserts
- Use background job processing for non-critical tracking

---

## Performance Metrics (Expected Improvements)

| Issue | Before | After | Improvement |
|-------|--------|-------|-------------|
| External API blocking | 5-30s | <1s | **95%+ faster** |
| VIP tracks query (50 tracks) | 100+ queries | 1 query | **100x faster** |
| Featured tracks query (20 tracks) | 40+ queries | 1 query | **40x faster** |
| Playlist stats query | N+1 queries | 1 query | **Significant** |

---

## Testing Recommendations

1. **Load Time Testing:**
   - Test homepage with slow external API (simulate timeout)
   - Test with large track databases (1000+ tracks)
   - Monitor database query counts

2. **Database Performance:**
   - Run `EXPLAIN` on optimized queries
   - Verify indexes are being used
   - Monitor slow query log

3. **API Response Times:**
   - Monitor `/api/get_vip_sample_tracks.php` response time
   - Monitor `/api/get_featured_tracks.php` response time
   - Add response time logging

---

## Next Steps

1. ✅ **COMPLETED:** Fix blocking external API call
2. ✅ **COMPLETED:** Fix N+1 queries in track APIs
3. ✅ **COMPLETED:** Optimize playlist queries
4. ⏳ **TODO:** Add database indexes (verify/create)
5. ⏳ **TODO:** Consolidate duplicate playlist loading
6. ⏳ **TODO:** Implement API response caching
7. ⏳ **TODO:** Add performance monitoring

---

## Files Modified

1. `includes/security_tracking.php` - Fixed blocking API call
2. `api/get_vip_sample_tracks.php` - Fixed N+1 queries
3. `api/get_featured_tracks.php` - Fixed N+1 queries
4. `api/get_community_playlists.php` - Fixed N+1 queries
5. `includes/header.php` - Minor query optimization

---

## Estimated Total Impact

**Before:** 30+ seconds load time  
**After:** Expected < 3 seconds load time  
**Improvement:** **90%+ faster**

The homepage should now load significantly faster, especially when:
- External APIs are slow
- Database has many tracks
- Multiple users are accessing simultaneously