Suggestions for WishTV Development Team - Database Optimization

fragmented_tables.md

Table Fragmentation Analysis

Database	Table	Data Size (MB)	Index Size (MB)	Free Space (MB)	Fragmentation %	Potential Space Recovered (MB)
pantheon	import_byline	1.52	0.00	4.00	263.92	4.00
pantheon	chk	1.52	0.00	4.00	263.92	4.00
pantheon	wp_96options	20.06	6.50	66.00	248.47	66.00
pantheon	wp_96termmeta	0.42	1.36	4.00	224.56	4.00
pantheon	wp_96_attachments	1.52	0.42	4.00	206.45	4.00
pantheon	wp_96term_taxonomy	1.52	0.75	4.00	176.55	4.00
pantheon	wp_96yoast_seo_meta	3.52	0.00	4.00	113.78	4.00
pantheon	wp_96temp_attachment_metadata	3.52	0.00	4.00	113.78	4.00
pantheon	wp_96redirection_logs	4.02	0.91	5.00	101.59	5.00
pantheon	wp_96as3cf_items	3.52	0.58	4.00	97.71	4.00
pantheon	wp_96terms	1.52	3.03	4.00	87.97	4.00
pantheon	wp_96fuel_posts_hash	4.52	1.52	4.00	66.32	4.00
pantheon	wp_96usermeta	1.48	4.81	4.00	63.52	4.00
pantheon	wp_96redirection_items	3.52	3.61	4.00	56.14	4.00
pantheon	wp_96yoast_prominent_words	2.52	6.55	4.00	44.14	4.00
pantheon	wp_96redirection_404	130.14	57.36	54.00	28.80	54.00
pantheon	wp_96yoast_primary_term	8.52	12.03	4.00	19.47	4.00
pantheon	wp_96posts_march	47.56	2.84	6.00	11.90	6.00
pantheon	wp_96yoast_indexable_hierarchy	31.16	36.48	7.00	10.35	7.00

Total Space Recovered

Total Potential Space Recovered via Maintenance and Optimization (MB): 247.00

indexes.md

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_96posts queries
2	216,255s	104,414	2.07s	SELECT	wp_96postmeta queries
3	155,815s	8,424	18.50s	SELECT	wp_96posts complex queries
4	150,328s	8,375	17.95s	SELECT	wp_96posts complex queries
5	15,292s	4,965	3.08s	SELECT	wp_96posts + wp_96term_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_96posts and wp_96postmeta 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_96posts` 
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_96postmeta`

🗂️ Index Analysis & Recommendations

📋 Query Analysis for Index Optimization

Rank	Query Pattern	Frequency	Total Time	Index Recommendations
1	wp_96posts basic queries	115,869	220,186s	Composite index on (post_type, post_status, post_date)
2	wp_96postmeta queries	104,414	216,255s	Composite index on (post_id, meta_key)
3	wp_96posts complex queries	8,424	155,815s	Multiple indexes for different query patterns
4	wp_96posts + 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_96posts: type_status_date (post_type, post_status, post_date, post_author)
• wp_96postmeta: meta_key (meta_key, meta_value(32), post_id, meta_id) + post_id (post_id, meta_key(32), meta_value(32), meta_id)
• wp_96comments, wp_96users, wp_96usermeta, wp_96options, 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_96term_relationships Table (Taxonomy Queries)

-- For taxonomy queries (plugin doesn't handle this table)
CREATE INDEX idx_term_relationships_object ON wp_96term_relationships(object_id, term_taxonomy_id);
CREATE INDEX idx_term_relationships_term ON wp_96term_relationships(term_taxonomy_id, object_id);

wp_96yoast_indexable Table (Yoast SEO)

-- For SEO queries (plugin doesn't handle Yoast tables)
CREATE INDEX idx_yoast_indexable_object ON wp_96yoast_indexable(object_id, object_type);
CREATE INDEX idx_yoast_indexable_type_status ON wp_96yoast_indexable(object_type, object_sub_type, post_status);

wp_96yoast_seo_links Table (Yoast SEO)

-- For internal linking (plugin doesn't handle Yoast tables)
CREATE INDEX idx_yoast_links_url ON wp_96yoast_seo_links(url);
CREATE INDEX idx_yoast_links_post ON wp_96yoast_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

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

🚀 Additional Optimization Tips

• Table Maintenance: Run OPTIMIZE TABLE after adding indexes (or use WP-CLI with wp db optimize)
• 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
4. 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
Format: Complete Report with Index Recommendations
Status: 🚨 CRITICAL - Immediate action required