Working with MySQL database engines

Working with MySQL Database Engines

Get an overview of how MySQL database engines work, including clear steps, practical guidance, and useful SQL examples.

This guide introduces the basics of MySQL database engines.

About MySQL Database Engines
Database engines control how MySQL stores, manages, and retrieves data.

The two most commonly used MySQL engines are MyISAM and InnoDB.

• MyISAM is the default engine for MySQL versions below 5.5.5 and works efficiently in many situations.

• InnoDB may be a better option depending on your needs because it supports transactions and foreign keys—features MyISAM does not provide.

With root access, you can control how MySQL uses these engines, change the system default, adjust individual tables, and perform additional configuration.

Determining the Default Database Engine
At the mysql> prompt, run:

SHOW ENGINES;

MySQL will display a list of engines, descriptions, and supported features. The current default engine is labeled DEFAULT in the Support column.

Changing the Default Database Engine
After changing the default engine, all new tables will use the new engine unless another one is specified during creation.

Steps:

1. Open the my.cnf configuration file in a text editor.

   • AlmaLinux / Fedora: located in /etc

   • Debian / Ubuntu: located in /etc/mysql

2. Locate the [mysqld] section.

3. Add or update this line, replacing ENGINE with the engine you want:

default-storage-engine=ENGINE

Important
If enabling InnoDB, ensure the following line is disabled by adding # at the beginning:

#skip-innodb

4. Save and exit the file.

5. Restart MySQL:

   • AlmaLinux / Fedora:

     service mysqld restart

   • Debian / Ubuntu:

     service mysql restart

6. Use SHOW ENGINES again to confirm the new default engine.

Determining a Table’s Current Engine
Run the command below, replacing database with the database name:

SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'database';

This shows all tables and the engines they use.

Changing a Table’s Engine
To switch an existing table (for example, myTable) to InnoDB:

ALTER TABLE myTable ENGINE = InnoDB;

Creating a Table with a Specific Engine
To create a MyISAM table:

CREATE TABLE myTable (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       data VARCHAR(20) NOT NULL
) ENGINE MyISAM;
To create an InnoDB table:

CREATE TABLE myTable (
       id INT NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (id),
       data VARCHAR(20) NOT NULL
) ENGINE InnoDB;

More Information
Details on MyISAM: dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html
Details on InnoDB: dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html