Course
Storage Engines
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
Storage Engines
The MySQL Storage Engines
As we already know, a MySQL database is used to store data in the form of rows and columns. The MySQL storage engine is a component that is used to handle the SQL operations performed to manage this data. They work with simple tasks like creating a table, renaming it, updating or deleting it; which is necessary to increase the database performance.
There are two categories of storage engines used: transactional engines and non-transactional engines. Many common storage engines fall into either type of these categories. In MySQL, however, the default storage engine is InnoDB.
Common Storage Engines
Various common storage engines that are used to work with MySQL are as follows
InnoDB Storage Engine
- ACID Compliant − InnoDB is the default storage engine in MySQL 5.5 and later versions. It is a transactional database engine, ensuring ACID compliance, which means it supports operations like commit and rollback.
- Crash-Recovery − InnoDB offers crash-recovery capabilities to protect user data.
- Row-Level Locking − It supports row-level locking, which enhances multi-user concurrency and performance.
- Referential Integrity − It also enforces FOREIGN KEY referential-integrity constraints.
ISAM Storage Engine
- Deprecated − ISAM, which stands for Indexed Sequential Access Method, was supported by earlier MySQL versions but has been deprecated and removed from recent versions.
- Limited Size − ISAM tables were limited to a size of 4GB.
MyISAM Storage Engine
- Portability − MyISAM is designed for portability, addressing ISAM's non-portable nature.
- Performance − It offers faster performance compared to ISAM and was the default storage engine before MySQL 5.x.
- Memory Efficiency − MyISAM tables have a small memory footprint, making them suitable for read-only or read-mostly workloads.
MERGE Storage Engine
- Logical Combination − MERGE table enables a MySQL developer to logically combine multiple identical MyISAM tables and reference them as one object.
- Limited Operations − Only INSERT, SELECT, DELETE, and UPDATE operations are allowed on MERGE tables. If DROP query is used, only the storage engine specification gets reset while the table remains unchanged.
MEMORY Storage Engine
- In-Memory Storage − MEMORY tables store data entirely in RAM, optimizing access speed for quick lookups.
- Hash Indexes − It uses hash indexes for faster data retrieval.
- Decreasing Use − Its use cases are decreasing; other engines, like InnoDB's buffer pool memory area provide better memory management.
CSV Storage Engine
- CSV Format − CSV tables are text files with comma-separated values, useful for data exchange with scripts and applications.
- No Indexing − They are not indexed, and generally used during data import or export alongside InnoDB tables.
NDBCLUSTER Storage Engine
- Clustering − NDBCLUSTER, also known as NDB, is a clustered database engine suitable for applications that require the highest possible degree of uptime and availability.
ARCHIVE Storage Engine
- Historical Data − ARCHIVE tables are ideal for storing and retrieving large amounts of historical, archived, or secure data. The ARCHIVE storage engines support supports non-indexed tables
BLACKHOLE Storage Engine
- Data Discard − BLACKHOLE tables accept data but do not store it, always returning an empty set.
- Usage − Used in replication configurations, where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.
FEDERATED Storage Engine
- Distributed Databases − FEDERATED allows linking separate MySQL servers to create a logical database from multiple physical servers, useful in distributed environments.
EXAMPLE Storage Engine
- Development Tool − EXAMPLE is a tool in the MySQL source code that serves as an example for developers to start writing new storage engines. You can create tables with this engine, but it doesn't store or retrieve data.
Even though there are so many storage engines that can be used with databases, there is no such thing called a perfect storage engine. In some situations, one storage engine could be a better fit to use whereas in other situations, other engines perform better. Therefore, one must carefully choose what Storage engine to use while working in certain environments.
To choose an engine, you can use the SHOW ENGINES statement.
SHOW ENGINES Statement
The SHOW ENGINES statement in MySQL will list out all the storage engines. It can be taken into consideration while choosing an engine that are supported by the database and are easy to work with.
Syntax
Following is the syntax of the SHOW ENGINES statement
SHOW ENGINES\Gwhere, the '\G' delimiter is used to vertically align the result-set obtained from executing this statement.
Example
Let us observe the result-set obtained by executing the SHOW ENGINES statement in a MySQL database using the following query
SHOW ENGINES\GOutput
Following is the result-set obtained. Here, you can check which storage engines are supported by the MySQL database and where they can be best used
*************************** 1. row ************************ Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO XA: NO Savepoints: NO*************************** 2. row ************************ Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO*************************** 3. row ************************ Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO*************************** 4. row ************************ Engine: FEDERATED Support: NO Comment: Federated MySQL storage engineTransactions: NULL XA: NULL Savepoints: NULL*************************** 5. row ************************ Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance SchemaTransactions: NO XA: NO Savepoints: NO*************************** 6. row ************************ Engine: MyISAM Support: YES Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO*************************** 7. row ************************ Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 8. row ************************ Engine: ndbinfo Support: NO Comment: MySQL Cluster system information storage engineTransactions: NULL XA: NULL Savepoints: NULL*************************** 9. row ************************ Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears)Transactions: NO XA: NO Savepoints: NO*************************** 10. row ************************ Engine: ARCHIVE Support: YES Comment: Archive storage engineTransactions: NO XA: NO Savepoints: NO*************************** 11. row ************************ Engine: ndbcluster Support: NO Comment: Clustered, fault-tolerant tablesTransactions: NULL XA: NULL Savepoints: NULL11 rows in set (0.00 sec）Setting a Storage Engine
Once a storage engine is chosen to be used on a table, you might want to set it while creating the database table. This is done by specifying the type of engine you want to use by adding its name in the CREATE TABLE statement.
If you do not specify the engine type, the default engine (InnoDB for MySQL) will be used automatically.
Syntax
Following is the syntax to set a storage engine in CREATE TABLE statement
CREATE TABLE table_name ( column_name1 datatype, column_name2 datatype, . . .) ENGINE = engine_name;Example
In this example, let us create a new table 'TEST' on MyISAM storage engine using the following query
CREATE TABLE TEST ( ROLL INT, NAME VARCHAR(25), MARKS DECIMAL(20, 2)) ENGINE = MyISAM;The result obtained is as shown below
Query OK, 0 rows affected (0.01 sec)But if we create a table on an engine that is not supported by MySQL, say FEDERATED, an error is raised
CREATE TABLE TEST ( ROLL INT, NAME VARCHAR(25), MARKS DECIMAL(20, 2)) ENGINE = FEDERATED;We get the following error
ERROR 1286 (42000): Unknown storage engine 'FEDERATED'Changing Default Storage Engine
MySQL also has provisions to change the default storage engine option in three ways
- Using '--default-storage-engine=name' server startup option.
- Setting 'default-storage-engine' option in 'my.cnf' configuration file.
- Using SET statement
Syntax
Let us see the syntax of using SET statement to change the default storage engine in a database
SET default_storage_engine = engine_name;Note − The storage engine for temporary tables, which were created with the CREATE TEMPORARY TABLE statement, can be set separately by setting the 'default_tmp_storage_engine', either at startup or at runtime.
Example
In this example, we are changing the default storage engine to MyISAM using SET statement given as follows
SET default_storage_engine = MyISAM;The result obtained is as follows
Query OK, 0 rows affected (0.00 sec)Now, let us list the storage engines using SHOW ENGINES statement below. The support column for MyISAM storage engine is changed to default
SHOW ENGINES\GOutput
Following is the result-set produced. Here, note that we are not displaying the entire result-set and only the MyISAM row for simpler understandability. The actual result-set has 11 total rows
*************************** 6. row ************************ Engine: MyISAM Support: DEFAULT Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO11 rows in set (0.00 sec)Altering Storage Engine
You can also alter the existing storage engine of a table to another storage engine using the ALTER TABLE command in MySQL. However, the storage engine must be changed to one that is supported by MySQL only.
Syntax
Following is the basic syntax to change the existing storage engine to another
ALTER TABLE table_name ENGINE = engine_name;Example
Consider the previously created table TEST on MyISAM database engine. In this example, using the following ALTER TABLE command, we are changing it to InnoDB engine.
ALTER TABLE TEST ENGINE = InnoDB;Output
After executing the above query, we get the following output
Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0Verification
To verify whether the storage engine is changed or not, use the following query
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testDB';The table produced is as shown below
Storage Engines Using a Client Program
We can also perform storage Engines using the client program.
Syntax
PHPNodeJSJavaPython
To show the storage engine through a PHP program, we need to execute the "SHOW ENGINES" statement using the mysqli function query() as follows
$sql = "SHOW ENGINES";$mysqli->query($sql);Example
Following are the programs
PHPNodeJSJavaPython
$dbhost = 'localhost';$dbuser = 'root';$dbpass = 'password';$db = 'TUTORIALS';$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit();}//printf('Connected successfully.');$sql = "SHOW ENGINES";if($mysqli->query($sql)){ printf("Show query executed successfully....!\n");}printf("Storage engines: \n");if($result = $mysqli->query($sql)){ print_r($result);}if($mysqli->error){ printf("Error message: ", $mysqli->error);}$mysqli->close();Output
The output obtained is as shown below
Show query executed successfully....!Storage engines:mysqli_result Object( [current_field] => 0 [field_count] => 6 [lengths] => [num_rows] => 11 [type] => 0)