In this article we will show you how to change the database engine of a MySQL database table.
A database engine is the fundamental software component of a Database Management System. A database engine is composed of the core building blocks on which a DBMS operates. It allows a DBMS to create, read, update and delete data from a database.
Quick Steps
1. Login to
cPanel and open phpMyAdmin.
2. Select the database table from the left hand column whose storage engine need to be changed.
3. Click on the Operations tab.
4. Under Table options, you would find a drop down called Storage Engine.
5. Select the Storage Engine of your choice.
6. when you are done, click on GO.
Login into cPanel , locate and open phpMyAdmin.
The domains section in cPanel.
From phpMyAdmin, select the database table you wish to edit from the left side and select operations.
Selecting a database in phpMyAdmin.
Now select the storage engine of your choice from the drop down.
Changing the database storage engine in phpMyAdmin.
When ready, click on Go to save the changes.
Also, we do have a second method. You could simply run the following SQL query on SQL tab after selecting the desired database from phpMyAdmin.
ALTER TABLE `table_name` ENGINE = MYISAMAfter the execution of this query the database engine of the selected database will be changed to MYISAM.
Conclusion
Done! You have successfully changed the storage engine for the table.
If the steps above listed in this article do not resolve your issue, please feel free to open a support ticket and we’d be happy to take a look.
Our Guiding Principles
- Provide consistent, stable, and reliable web hosting services.
- Ensure rapid ticket response and quick resolutions to issues.
- Never saturate or over-provision servers to ensure stability and speed for our customers.
- Use only high-quality enterprise-class hardware to ensure minimal downtime from hardware failures.
- Provide clear pricing with no hidden fees or gotchas.
Summary: in this tutorial, you will learn how to which storage engine that a table is using and how to change the storage engine of the table to a different one.
MySQL supports many kinds of storage engines that provide different capabilities and characteristics. For example, the InnoDB tables support transaction, whereas MyISAM does not.
Querying the current storage engine of a table
There are several ways to get the current storage engine of a table.
The first way to check the current storage engine of a table is to query data from the tables table in the information_schema database.
For example, to get the current storage engine of the offices table in the classicmodels sample database, you use the following query:
SELECT engine FROM information_schema.tables WHERE table_schema = 'classicmodels' AND table_name = 'offices';
Code language: SQL (Structured Query Language) (sql)The second way to query the storage engine of a table is to use the SHOW TABLE STATUS statement as follows:
SHOW TABLE STATUS LIKE 'offices';
Code language: SQL (Structured Query Language) (sql)The third way to get the storage engine of a table is to use the SHOW CREATE TABLE statement.
SHOW CREATE TABLE offices;
Code language: SQL (Structured Query Language) (sql)mysql> SHOW CREATE TABLE offices\G; *************************** 1. row *************************** Table: offices Create Table: CREATE TABLE `offices` ( `officeCode` varchar(10) NOT NULL, `city` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `state` varchar(50) DEFAULT NULL, `country` varchar(50) NOT NULL, `postalCode` varchar(15) NOT NULL, `territory` varchar(10) NOT NULL, PRIMARY KEY (`officeCode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql>
MySQL showed the offices table uses the InnoDB storage engine.
MySQL changing storage engine
Once you have the information of the storage engine of a table, you can change it using the ALTER TABLE statement as follows:
ALTER TABLE table_name ENGINE engine_name;
Code language: SQL (Structured Query Language) (sql)To check which storage engine that your MySQL server currently supports, you use the SHOW ENGINES statement as follows:
SHOW ENGINES;
Code language: SQL (Structured Query Language) (sql)For example, to change the storage engine of the offices table from InnoDB to MyISAM, you use the following statement:
ALTER TABLE offices ENGINE = 'MYISAM';
Code language: SQL (Structured Query Language) (sql)In this tutorial, we have shown you how to query the current storage engine of a table and how to change it to a different storage engine using the ALTER TABLE statement.
Was this tutorial helpful?