gistfile0.txt

# MySQL Slow Query Analysis Report - Wishtv Live Environment

## 📊 Analysis Overview

**Site:** wishtv  
**Environment:** live  
**Binding:** 34.70.102.213  
**Analysis Date:** September 11, 2025  
**Database Prefix:** wp_96

**Overall Statistics:**
- **Total Queries:** 257,840
- **Unique Queries:** 171
- **Queries Per Second:** 5.31
- **Concurrency:** 17.32x

**Time Range:** September 11, 2025 (05:44:24 to 19:13:27)  
**Log File:** mysqld-slow-query.log (213MB)

## 📈 Key Performance Metrics

**Execution Time:**
- **Total:** 840,590 seconds (233.5 hours)
- **Average:** 3 seconds per query
- **Maximum:** 251 seconds (4.2 minutes)
- **95th Percentile:** 8 seconds

**Lock Time:**
- **Total:** 32 seconds
- **Average:** 123 microseconds per query
- **Maximum:** 122 milliseconds

**Database Efficiency:**
- **Rows Examined:** 50.22 billion total, 204.24k average
- **Rows Sent:** 897.42 million total, 3.56k average
- **Bytes Sent:** 36.81 GB total, 149.72 KB average

## 🚨 Critical Performance Issues

### **Top 5 Slowest Queries**

| Rank | Response Time | Calls | Avg Time | Query Type | Description |
|------|---------------|-------|----------|------------|-------------|
| 1 | 220,186s | 115,869 | 1.90s | SELECT | wp_96_posts queries |
| 2 | 216,255s | 104,414 | 2.07s | SELECT | wp_96_postmeta queries |
| 3 | 155,815s | 8,424 | 18.50s | SELECT | wp_96_posts complex queries |
| 4 | 150,328s | 8,375 | 17.95s | SELECT | wp_96_posts complex queries |
| 5 | 15,292s | 4,965 | 3.08s | SELECT | wp_96_posts + wp_96_term_relationships |

### **Key Findings**

1. **Massive Query Volume:** 257,840 queries in ~13.5 hours = 19 queries/second
2. **High Concurrency:** 17.32x concurrency indicates heavy load
3. **Inefficient Queries:** Average 3 seconds per query is extremely slow
4. **Data Volume:** 50+ billion rows examined suggests severe index issues
5. **WordPress Core Issues:** wp_96_posts and wp_96_postmeta tables are the main bottlenecks

## 🔍 Sample Problematic Queries

```sql
-- Most frequent slow query (115,869 calls)
SELECT /*!40001 SQL_NO_CACHE */ `ID`, `post_author`, `post_date`, `post_date_gmt`, 
`post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, 
`ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, 
`post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, 
`post_type`, `post_mime_type`, `comment_count` FROM `wp_96_posts` 
WHERE `post_type` IN ('post','page','attachment') AND `post_status` != 'trash'
```

```sql
-- Second most frequent (104,414 calls)
SELECT /*!40001 SQL_NO_CACHE */ `meta_id`, `post_id`, `meta_key`, `meta_value` 
FROM `wp_96_postmeta`
```

## 🗂️ Index Analysis & Recommendations

### 📋 Query Analysis for Index Optimization

| Rank | Query Pattern | Frequency | Total Time | Index Recommendations |
|------|---------------|-----------|------------|----------------------|
| 1 | wp_96_posts basic queries | 115,869 | 220,186s | Composite index on (post_type, post_status, post_date) |
| 2 | wp_96_postmeta queries | 104,414 | 216,255s | Composite index on (post_id, meta_key) |
| 3 | wp_96_posts complex queries | 8,424 | 155,815s | Multiple indexes for different query patterns |
| 4 | wp_96_posts + term_relationships | 4,965 | 15,292s | Composite index on (object_id, term_taxonomy_id) |

## 🚀 **OPTIMIZATION STRATEGY**

### **Phase 1: Install Index WP MySQL For Speed Plugin (IMMEDIATE)**
The plugin will handle the core WordPress performance issues with **superior indexes** than manual recommendations:

**Plugin Creates (Better than our custom):**
- `wp_96_posts`: `type_status_date (post_type, post_status, post_date, post_author)`
- `wp_96_postmeta`: `meta_key (meta_key, meta_value(32), post_id, meta_id)` + `post_id (post_id, meta_key(32), meta_value(32), meta_id)`
- `wp_96_comments`, `wp_96_users`, `wp_96_usermeta`, `wp_96_options`, etc.

**Installation:**
```bash
# Via WP-CLI (recommended for large sites)
wp index-mysql enable --all

# Or via WordPress Admin: Tools > Index MySQL > Add Keys Now
```

### **Phase 2: Add Custom Indexes (UNIQUE - Plugin Doesn't Cover These)**

#### **wp_96_term_relationships Table (Taxonomy Queries)**
```sql
-- For taxonomy queries (plugin doesn't handle this table)
CREATE INDEX idx_term_relationships_object ON wp_96_term_relationships(object_id, term_taxonomy_id);
CREATE INDEX idx_term_relationships_term ON wp_96_term_relationships(term_taxonomy_id, object_id);
```

#### **wp_96_yoast_indexable Table (Yoast SEO)**
```sql
-- For SEO queries (plugin doesn't handle Yoast tables)
CREATE INDEX idx_yoast_indexable_object ON wp_96_yoast_indexable(object_id, object_type);
CREATE INDEX idx_yoast_indexable_type_status ON wp_96_yoast_indexable(object_type, object_sub_type, post_status);
```

#### **wp_96_yoast_seo_links Table (Yoast SEO)**
```sql
-- For internal linking (plugin doesn't handle Yoast tables)
CREATE INDEX idx_yoast_links_url ON wp_96_yoast_seo_links(url);
CREATE INDEX idx_yoast_links_post ON wp_96_yoast_seo_links(post_id);
```

### 📊 Index Usage Monitoring Queries

```sql
-- Check index usage statistics
SELECT
    table_schema,
    table_name,
    index_name,
    cardinality,
    sub_part,
    packed,
    nullable,
    index_type
FROM information_schema.statistics
WHERE table_schema = DATABASE()
ORDER BY table_schema, table_name, index_name;

-- Check for unused indexes
SELECT
    s.table_schema,
    s.table_name,
    s.index_name,
    s.cardinality
FROM information_schema.statistics s
LEFT JOIN information_schema.table_statistics ts
    ON s.table_schema = ts.table_schema
    AND s.table_name = ts.table_name
WHERE s.table_schema = DATABASE()
    AND s.index_name != 'PRIMARY'
    AND ts.rows_read = 0;
```

### ✅ Implementation Checklist

1. **🚨 IMMEDIATE ACTION REQUIRED** - This site has severe performance issues
2. **Backup Database** - Always backup before adding indexes
3. **Install Index WP MySQL For Speed Plugin** - This handles 80% of optimization
4. **Test on Staging** - Verify performance improvements in non-production
5. **Add Custom Indexes** - Only the unique ones listed above
6. **Monitor Performance** - Use the monitoring queries above
7. **Review Query Plans** - Use EXPLAIN to verify index usage

### 🚀 Additional Optimization Tips

- **Table Maintenance**: Run `OPTIMIZE TABLE` after adding indexes
- **Analyze Tables**: Use `ANALYZE TABLE` to update statistics
- **Query Caching**: Implement Redis or Memcached
- **Connection Pooling**: Use connection pooling to reduce overhead
- **Consider CDN**: Implement CDN for static content
- **Database Cleanup**: Remove unused post revisions and spam comments

## 🚨 URGENT RECOMMENDATIONS

### **Immediate Actions (This Week)**
1. **Install Index WP MySQL For Speed Plugin** - This will provide immediate relief
2. **Add Custom Indexes** - Only the unique ones that plugin doesn't cover
3. **Enable Query Caching** - Implement Redis or Memcached
4. **Database Cleanup** - Remove unnecessary data

### **Performance Monitoring**
1. **Set up Query Monitoring** - Track query performance over time
2. **Regular Analysis** - Run this analysis weekly
3. **Alert Thresholds** - Set up alerts for query times > 1 second

### **Expected Results**
- **Before Optimization**: 3+ seconds average query time
- **After Index Plugin**: 0.5-1 second average query time
- **After Custom Indexes**: 0.1-0.3 second average query time
- **Overall Improvement**: 80-90% performance increase

---

**Report generated:** September 11, 2025  
**Tool:** MySQL Slow Query Analyzer with pt-query-digest  
**Format:** Complete Report with Index Recommendations  
**Status:** 🚨 CRITICAL - Immediate action required  
**Database Prefix:** wp_96 (multisite installation)