• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / How to tell which storage engine a MySQL table uses

How to tell which storage engine a MySQL table uses

MySQL supports multiple storage engines (e.g. MyISAM, INNODB, etc) each with its pros and cons, and each table in a MySQL database can use a different storage engine. This post looks at how to work out which table storage engine is used by a MySQL table, using either a SQL query or using the web browser tool phpMyAdmin. The next MySQL post shows how to change the storage engine for a MySQL table.

SQL Query

After digging around in the phpMyAdmin code I worked out they determine the MySQL table storage engine by querying the INFORMATION_SCHEMA database. This is a special database which decribes information relating to the various databases on the server.

The query the "products" table of the "test" database to see which storage engine it is using, you would run this SQL query:

SELECT ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'products'

The TABLE_SCHEMA is the name of the database, and TABLE_NAME is the table name you wish to query. The SQL query above will return the storage engine, assuming the database and table specified exists, and you have sufficient permissions.

If you wanted to see the storage engine for all tables in your database, do this instead:

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'

phpMyAdmin

Rather than write a SQL query, you can easily see the storage engine for all tables using the phpMyAdmin tool. Simply log in and select the database and you’ll see something like this:

viewing the mysql table storage engines in phpmyadmin

The column highlighted with the red box shows the storage engine used for each table. The last line with the bold storage engine shows the default storage engine for the database.

How to change the storage engine

Read my other posts about changing the MySQL storage engine and a PHP script to convert all tables.

Check Out These Related posts:

  1. MySQL utility commands
  2. Find which tables use INNODB with MySQL
  3. How to change the storage engine a MySQL table uses
  4. Use one file per table with MySQL’s INNODB storage engine

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap