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

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

🔍 Sample Problematic Queries

-- 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'

-- 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:

# 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)

-- 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)

-- 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)

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

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

🚨 IMMEDIATE ACTION REQUIRED - This site has severe performance issues
Backup Database - Always backup before adding indexes
Install Index WP MySQL For Speed Plugin - This handles 80% of optimization
Test on Staging - Verify performance improvements in non-production
Add Custom Indexes - Only the unique ones listed above
Monitor Performance - Use the monitoring queries above
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)

Install Index WP MySQL For Speed Plugin - This will provide immediate relief
Add Custom Indexes - Only the unique ones that plugin doesn't cover
Enable Query Caching - Implement Redis or Memcached
Database Cleanup - Remove unnecessary data

Performance Monitoring

Set up Query Monitoring - Track query performance over time
Regular Analysis - Run this analysis weekly
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)

miriamgoldman/wishtv_complete_report_updated.md