How to Convert MyISAM Tables to InnoDB on a WordPress WooCommerce Site

When managing a WordPress WooCommerce site, ensuring optimal database performance is critical. One common optimization is converting MyISAM tables to InnoDB, a more modern and efficient storage engine for MySQL.

In this article, we’ll explain what MyISAM and InnoDB are, why you might want to switch, and several methods to convert your database tables safely.

What Are MyISAM and InnoDB?

MyISAM and InnoDB are two types of storage engines used by MySQL to manage how data is stored and retrieved.

MyISAM

• Older engine used by default in earlier versions of MySQL
• Faster on read-heavy operations
• Lacks support for transactions and foreign key constraints
• Table-level locking (less efficient under high traffic)

InnoDB

• The default engine for MySQL 5.5+ and MariaDB
• Supports transactions, row-level locking, and foreign keys
• More crash-resilient and generally better for high-traffic websites like WooCommerce stores

Why Convert to InnoDB?

Switching to InnoDB is beneficial for several reasons:

• Improved performance under concurrent read/write operations
• Better data integrity with ACID-compliant transactions
• Row-level locking reduces table contention
• Required or recommended by some WooCommerce features

Before You Start: Backup First!

Before making any changes to your database:

• Create a full backup of your database
• Ideally, test the conversion process on a staging site before applying it to live

Method 1: Convert Tables Using LiteSpeed WordPress Plugin

If your Ecenica hosting account is on LiteSpeed web server, the LiteSpeed Cache plugin for WordPress offers a conversion tool.

1. Install and activate the LiteSpeed Cache plugin
2. Go to LiteSpeed Cache > Toolbox > DB Optimization
3. Scroll to the “Convert Tables to InnoDB” section
4. Click Convert next to any MyISAM tables listed

Method 2: Convert Tables Using cPanel phpMyAdmin

1. Login into your Ecenica cPanel control panel. See Where do I login to my hosting control panel
2. Click phpMyAdmin
3. Select your WordPress database from the left panel
4. Click on a table using the MyISAM engine
5. Click the SQL tab and run this SQL command:

   ALTER TABLE table_name ENGINE=InnoDB;
   

   Replace table_name with your actual table name (e.g. wp_posts).

6. Repeat for each MyISAM table or run a query to convert all in one go:

   SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') 
   FROM information_schema.tables 
   WHERE table_schema = 'your_database_name' AND engine = 'MyISAM';
   

   Copy the results and run them in the SQL tab to convert all tables.

Method 3: Convert Tables Using WP-CLI

1. Access your Ecenica hosting account SSH or use our built in Terminal
2. Navigate to your WordPress directory

3. Run this command to check MyISAM tables:

   wp db query "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND ENGINE = 'MyISAM';"
   

4. To convert a specific table:

   wp db query "ALTER TABLE wp_posts ENGINE=InnoDB;"
   

5. To convert all at once, combine with a script or generate SQL using a query as in phpMyAdmin method above.

Method 4: Convert Tables via cPanel Terminal

1. Login into your Ecenica cPanel control panel. See Where do I login to my hosting control panel
2. Open the Terminal feature. See How to Access Terminal

3. Access MySQL:

   mysql -u your_db_user -p your_db_name
   

4. Once logged in, run:

   SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_db_name' AND engine = 'MyISAM';
   

5. Use the results to run:

   ALTER TABLE table_name ENGINE=InnoDB;
   

6. Type exit to leave MySQL.

Final Notes

• Always test on a staging site first if possible
• Monitor your site after conversion for any plugin compatibility issues
• If you’re unsure about converting a specific table (like a custom plugin table), consult the plugin developer

By converting to InnoDB, you’ll help your WooCommerce site scale better, reduce database locking issues, and take advantage of modern MySQL features.