User Tools

Site Tools


mysql_engines

Find the Total number of tables/engines (All Databases)

select engine,count(*),sum(index_length+data_length)/1024/1024 "Table Engine Size in MB" from information_schema.tables group by engine;


Find the Engines for a specific db

Replace magento with your db name

select engine,count(*),sum(index_length+data_length)/1024/1024 "Table Engine Size in MB" from information_schema.tables where table_schema = 'magento' group by engine;


Find Engines for a specific db (Either InnoDB or MyISAM)

Replace magento with your db name and InnoDB with your desired engine

select engine,count(*),sum(index_length+data_length)/1024/1024 "Table Engine Size in MB" from information_schema.tables where table_schema = 'magento' and engine = 'InnoDB' group by engine;

Find Engine for a Specific Table (Specific Database)

Repalce database_name and table_name to your desired values

select engine, table_name from information_schema.tables where table_schema = 'database_name' and table_name = 'table_name';


Find Engine for a range of tables

Replace magento and %xml%

select engine, table_name from information_schema.tables where table_schema = 'magento' and table_name like '%xml%';

Find a Range of Tables with a Specific Engine

select engine, table_name from information_schema.tables where table_schema = 'magento' and table_name like '%xml%' and engine = 'InnoDB';

mysql_engines.txt · Last modified: 2024/05/23 07:26 by 127.0.0.1

Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki