# Working with MySQL database engines

> Learn how MySQL storage engines such as InnoDB and MyISAM differ, how to check which engine a table uses and how to convert it.

Source: https://helpcenter.ruachost.com/developers/databases/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.

> **Note:**
Some procedures in this guide require root access to the server.

**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.

> **Note:**
This guide assumes you already know how to access MySQL through the command line using the `mysql` tool.

**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