Linux 4 All

Best Linux tricks source

Posts in the information_schema category

Here is how:

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics WHERE index_type LIKE ‘FULLTEXT%’;

This is important as MySQL prior to 5.6 does not support FULLTEXT InnoDB tables.

Get MySQL InnoDB tables

Run this query to get all InnoDB tables:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

Change engine='innodb' to engine='myisam' to get MyISAM tables.

This query will display both the total size and number of tables for each MySQL engine.

SELECT engine,ROUND(SUM(index_length+data_length)/1024/1024,2) AS 'size in MB',count(engine) FROM information_schema.tables GROUP BY engine;