Aug 172014
 
Article MySQL

Tables in a MySQL database can use  different types of storage engines, the most common among them being MyISAM and InnoDB. Each of these types of storage has advantages and drawbacks. The decision about which storage type es most adequate for a given table depends on several factors.

Anyway, the first step is knowing which type of storage engine is used by existing tables in a database. This post explains several ways to obtain this information.

Using a SQL query on “information_schema”

The most straightforward way to learn the storage type of tables in a MySQL database is issuing a query against the “INFORMATION_SCHEMA.TABLES” table (“INFORMATION_SCHEMA” is a special database used for the administration of other MySQL databases existing in a MySQL instance. It stores information about different objects in those databases):

For instance, if the above sentence is run on a WordPress database, we may get the following result:

As we can see, all base tables of the WordPress installation are using MyISAM, whilst tree tables created by the “NextGENGallery” plugin are using InnoDB.

Using phpMyAdmin

If the MySQL database administration tool phpMyAdmin is present in the system, the type of storage engine used by tables in the database can also be displayed under the column “Type” in the table listing screen:

motor-de-almacenamiento-mysql

The value of the column “Type” yn the last row is the default storage engine that will be used when a new table is created, unless a different storage engine is explicitly specified in the “CREATE TABLE” statement.

Index of posts about MySQL administration and usage

 Posted by at 2:42 pm

 Leave a Reply

(required)

(required)