Differences

This shows you the differences between two versions of the page.

--- mysql_engines [2017/03/07 10:22] – luke7858
+++ mysql_engines [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-=== Find the Total number of tables/engines ===
+=== Find the Total number of tables/engines (All Databases) ===
 <sxh bash>
 select engine,count(*),sum(index_length+data_length)/1024/1024 "Table Engine Size in MB" from information_schema.tables group by engine;
@@ Line 5: / Line 5: @@
 \\
 \\
-=== Find Engine for a Specific Table ===
+=== Find the Engines for a specific db ===
+Replace **__magento__** with your db name
+<sxh bash>
+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;
+</sxh>
+\\
+\\
+=== Find Engines for a specific db (Either InnoDB or MyISAM) ===
+Replace **__magento__** with your db name and **InnoDB** with your desired engine
+<sxh bash>
+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;
+</sxh>
+\\
+=== Find Engine for a Specific Table (Specific Database) ===
 Repalce **__database_name__** and **__table_name__** to your desired values
 <sxh bash>
 select engine, table_name from information_schema.tables where table_schema = 'database_name' and table_name = 'table_name';
 </sxh>
+\\
 \\
 === Find Engine for a range of tables ===
@@ Line 19: / Line 33: @@
 === Find a Range of Tables with a Specific Engine ===
 <sxh bash>
-select engine, table_name from information_schema.tables where table_schema = 'magento' and table_name like '%xml%' and engine = InnoDB';
+select engine, table_name from information_schema.tables where table_schema = 'magento' and table_name like '%xml%' and engine = 'InnoDB';
 </sxh>