Delving into MariaDB Engine Details: SHOW ENGINE in Action

1. Variations
   There are actually multiple variations of SHOW ENGINE depending on what information you need. Some common ones include:

   • SHOW ENGINES: This lists all the storage engines available on your MariaDB server.
   • SHOW ENGINE <engine_name> STATUS: This provides detailed information about a specific storage engine, like InnoDB, the default engine in MariaDB.

2. Information Provided
   The specific details returned will vary depending on the engine and the variation of the command used. However, some common information you might see include:

   • Engine name and version
   • Storage engine capabilities (e.g., transactions, full-text search)
   • Status information (e.g., if the engine is running, any errors)

3. Usage
   SHOW ENGINE statements are typically used by database administrators to monitor and manage their MariaDB server. They are not typically used in regular data manipulation queries.

---

List All Available Storage Engines

SHOW ENGINES;

This will display a list of all storage engines installed on your MariaDB server. The output will include information like the engine name, its support status (default or not), and a brief comment.

Get Detailed Information on a Specific Engine

SHOW ENGINE INNODB STATUS;

This will show detailed information about the InnoDB storage engine, including its version, capabilities (transactions, XA, etc.), and any relevant status information.

Using information_schema for Engine Details

MariaDB also provides information about storage engines through the information_schema.ENGINES table. You can query this table to get details similar to SHOW ENGINE.

SELECT * FROM information_schema.ENGINES;

This will list all storage engines and their details similar to SHOW ENGINES.

---

1. Information Schema Tables
   

• information_schema.TABLES: This table stores information about all tables in your database, including the storage engine used for each table. You can query this table to identify the engine used by specific tables.

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES;

1. EXPLAIN Statement
   

• The EXPLAIN statement can be used to analyze the query execution plan. While it doesn't directly tell you the engine, it can sometimes reveal information about how the storage engine is used for a particular query. This might be helpful for understanding how the engine is involved in processing specific data access requests.

1. Server Configuration Files
   

• In some cases, the MariaDB server configuration files might contain information about the default storage engine or any specific engine configurations. However, this approach is less recommended as it involves server configuration details and might not be as user-friendly for quick checks.