Skip to main content
Documents
Clauses StatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL SHOW ENGINES Statements

The `SHOW ENGINES` statement in MySQL is used to list all available storage engines and their current status. It provides details about supported, available storage engines and identifies the default engine in the MySQL server.

Usage

The `SHOW ENGINES` statement is typically used to identify the storage engines available in your MySQL server, along with their supported features and current status. This is crucial for database administrators when configuring databases or optimizing performance.

SHOW ENGINES;

This syntax outputs a list of all storage engines, detailing their support status, availability, and any additional information.

Note: A storage engine is a component of MySQL that handles SQL operations for different table types. Each engine has its own features and benefits, affecting performance and storage capabilities.

The `SHOW ENGINES` output includes columns such as:

  • ENGINE: The name of the storage engine.
  • SUPPORT: The status of the engine, which can be 'YES' (available), 'NO' (unavailable), or 'DEFAULT' (the default engine).

Examples

1. Basic Usage

SHOW ENGINES;

This simple command retrieves a list of all storage engines supported by the MySQL server, along with their characteristics.

2. Checking Default Storage Engine

SELECT ENGINE
FROM information_schema.ENGINES
WHERE SUPPORT = 'DEFAULT';

This query checks the `information_schema` to find out which storage engine is set as the default, providing more flexibility in the query.

Tips and Best Practices

  • Regularly check available engines. Use `SHOW ENGINES` to stay informed about which engines are supported in your MySQL version, especially after updates.
  • Understand engine features. Familiarize yourself with the capabilities of each engine to choose the best one for your specific application needs.
  • Keep performance in mind. Different engines offer various performance benefits; use `SHOW ENGINES` to help decide which engine best suits your database performance requirements.
  • Verify engine availability. Before developing or deploying an application, confirm that the required storage engine is supported and available on your server.

Example Scenario

Choosing the right storage engine can significantly impact your application's performance. For instance, using `InnoDB` for transactions and foreign key support can enhance data integrity, whereas `MyISAM` might be preferred for applications requiring fast read operations with fewer write transactions.

SQL Upskilling for Beginners

Gain the SQL skills to interact with and query your data.
Start Learning for Free