🛒 WooCommerce Database = Speed Optimization
E-commerce databases grow exponentially. Smart optimization strategies keep WooCommerce stores fast, even with thousands of products and orders.
📝 WooCommerce-Specific Optimization
# Clean Order Meta Data
-- Remove orphaned order item 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 (old sessions)
DELETE FROM wp_woocommerce_sessions
WHERE session_expiry < UNIX_TIMESTAMP();
# Clean Attribute Taxonomy
-- Remove unused 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;
# Product Meta Cleanup
-- Remove orphaned product meta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
# Optimize Order Statistics
-- Regenerate order stats (if using WooCommerce)
UPDATE wp_wc_order_stats os
INNER JOIN wp_posts p ON os.order_id = p.ID
SET os.status = p.post_status;
# Schedule Regular Cleanup
-- Monthly cleanup script
CREATE EVENT monthly_cleanup
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
-- Delete old order sessions (30 days)
DELETE FROM wp_woocommerce_sessions
WHERE session_expiry < UNIX_TIMESTAMP() - 2592000;
-- Delete expired coupons
DELETE FROM wp_posts
WHERE post_type = 'shop_coupon'
AND post_status = 'expired'
AND post_modified < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- Clean transients
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
OR option_name LIKE '_transient_timeout_%';
END;
🎯 Performance Monitoring Queries
# Check 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;
# Find Slow Running 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 Usage
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;
# Index Usage Statistics
SELECT
table_name,
index_name,
cardinality,
(cardinality / (
SELECT COUNT(*)
FROM information_schema.statistics s2
WHERE s2.table_name = s.table_name
AND s2.table_schema = s.table_schema
)) * 100 as index_usage_percentage
FROM information_schema.statistics s
WHERE table_schema = 'your_database'
AND table_name LIKE 'wp_%'
ORDER BY cardinality DESC;
✅ E-Commerce Database Best Practices
- ✓ Implement database partitioning for large product catalogs
- ✓ Use Redis or Memcached for object caching
- ✓ Schedule regular optimization during off-peak hours
- ✓ Monitor slow query logs weekly
- ✓ Consider dedicated database server for high traffic stores
"E-commerce databases require special attention. A single slow query can cost thousands in lost sales. Regular optimization is not optional—it's essential."
