Bits of .NET

🛒 E-Commerce Database = Speed Optimization

E-commerce databases grow fast. Smart optimization keeps WooCommerce fast with thousands of products and orders.

📝 E-Commerce Optimization

# Clean Order Meta
DELETE om FROM wp_woocommerce_order_itemmeta om
LEFT JOIN wp_woocommerce_order_items oi 
    ON om.order_item_id = oi.order_item_id
WHERE oi.order_item_id IS NULL;

# Clean Session Data
DELETE FROM wp_woocommerce_sessions
WHERE session_expiry < UNIX_TIMESTAMP();

# Remove Old Coupons
DELETE FROM wp_posts 
WHERE post_type = 'shop_coupon' 
AND post_status = 'expired'
AND post_modified < DATE_SUB(NOW(), INTERVAL 30 DAY);

# Product Meta Cleanup
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

# Optimize Product Attributes
DELETE t FROM wp_terms t
LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
WHERE tt.taxonomy LIKE 'pa_%' 
AND tt.count = 0;

🎯 Performance Monitoring

# Database Size Breakdown
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_size_mb,
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_size_mb,
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
GROUP BY table_schema;

# Slow Queries
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
WHERE sql_text LIKE '%wp_posts%'
ORDER BY query_time DESC
LIMIT 10;

# Product Variation Memory
SELECT 
    p.ID,
    p.post_title,
    COUNT(pm.meta_id) as meta_count,
    ROUND(LENGTH(pm.meta_value) / 1024, 2) as meta_size_kb
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'product_variation'
GROUP BY p.ID
HAVING meta_count > 20
ORDER BY meta_size_kb DESC
LIMIT 20;

✅ E-Commerce Best Practices

• ✓ Implement database partitioning
• ✓ Use Redis/Memcached for object caching
• ✓ Schedule optimization during off-peak hours
• ✓ Monitor slow query logs weekly
• ✓ Consider dedicated database server