# Query Performance Investigation

## Current Status

After adding indexes and `noload()`, the query should be fast but still reports slow performance.

## Diagnostics Added

Added detailed timing logs to `/api/music/` endpoint to identify the bottleneck:

```python
logger.info(f"Music list query: total={total_time:.3f}s, query={query_time:.3f}s, fetch={fetch_time:.3f}s, rows={len(music_list)}, skip={skip}")
```

This will show:
- **total**: Total endpoint execution time
- **query**: Time spent in database query execution
- **fetch**: Time spent fetching/hydrating objects  
- **rows**: Number of rows returned
- **skip**: Pagination offset

## Check Logs After Deployment

```bash
kubectl logs -f deployment/youmusic | grep "Music list query"
```

Expected output:
```
Music list query: total=0.050s, query=0.020s, fetch=0.010s, rows=50, skip=0
```

## Possible Bottlenecks

### 1. Pydantic Serialization (Most Likely)
If `total` time is high but `query` and `fetch` are low, the issue is FastAPI's response model validation.

**Solution:** Use `response_model_exclude_unset=True` or custom serialization

### 2. Network Latency (NFS)
If `query` time is high, the issue is NFS storage latency.

**Solution:** Increase SQLite cache size, or consider local SSD cache

### 3. Object Hydration
If `fetch` time is high, SQLAlchemy is slow at creating Python objects.

**Solution:** Use raw SQL or optimize model loading

### 4. Lock Contention
If times vary wildly, there's database lock contention.

**Solution:** Check concurrent requests, adjust busy_timeout

## Next Steps Based on Logs

**If query time is high (>1s):**
- Check if indexes are used: `EXPLAIN QUERY PLAN`
- Increase cache size: `PRAGMA cache_size = -64000;` (64MB)
- Add composite index for file_exists + created_at

**If fetch time is high (>1s):**
- Use `defer()` to lazy-load large TEXT columns (lyrics)
- Profile object creation overhead

**If total - (query + fetch) is high (>1s):**
- It's Pydantic serialization
- Use custom JSON encoder
- Or use `response_model=None` and manual dict conversion

## Deploy and Check

```bash
git push
kubectl logs -f deployment/youmusic | grep "Music list query"
```

Then we'll know exactly where the bottleneck is!