Database Optimization: Cleaning Up Overhead & Indexing

1. Cleaning Up Overhead

What is overhead? Unused space from deleted/modified data (common in MySQL/InnoDB tables).

How to Fix:

• Optimize Tables (MySQL):

  
  OPTIMIZE TABLE wp_posts;  -- For WordPress
              

• Use phpMyAdmin:
  1. Select tables → Click Optimize Table.
• Automate with Plugins:

  Use WP-Optimize (WordPress).

2. Indexing for Speed

What are indexes? Database shortcuts to speed up queries (like a book’s index).

How to Add Indexes:

-- Example: Index for a WordPress posts table
ALTER TABLE wp_posts ADD INDEX idx_post_date (post_date);
    

Best Practices:

• Index columns used in WHERE, JOIN, or ORDER BY clauses.
• Avoid over-indexing (slows down writes).
• Use tools like MySQL Workbench to analyze queries.

3. Maintenance Schedule

• Weekly: Optimize tables with overhead > 10%.
• Monthly: Review slow queries and add indexes.