CodePuzzling::tumblr('!'); : [MySQL] Using information

[MySQL] Using information_schema instead of SHOW commands

PHP can handle data returned by SHOW commands, but information about all databases and tables are stored in information_schema database. Some useful queries:
1) Getting only columns name:

SELECT `column_name` 
FROM information_schema.columns
WHERE `table_name`="mytable";

The SHOW statement show columns from t5_user; shows extra information that must be filtered with PHP. In example if you want to know type, keys and so on for a particular field, it’s easy to use:

SHOW columns 
FROM table 
WHERE field = "myfield"

2) Getting last ID inserted, the number of records and the engine of a table:

select table_name as tables, engine,            
          table_rows as records, 
         auto_increment-1 as lastId  
FROM information_schema.tables  
WHERE table_name = "t5_user";

SHOW TABLE STATUS can retrieve the same information but from a shell it’s not so easy to read that bunch of data (yes you can use \G instead of ; at the end of the query to make data more readable). Obviously the last id can be retrieved with this simple query;

SELECT id 
FROM  MYDB.MYTABLE 
ORDER BY id DESC LIMIT 1

3) Getting the “heaviest” tables (those over 99.99 MB) in the entire DBMS

SELECT concat( table_schema, '.', table_name ) table_name, 
              concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
              concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
              concat( round( round( data_length + index_length,2 ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size 
FROM information_schema.TABLES 
HAVING data_length LIKE "___.%" 
OR data_length LIKE "____.%" 
ORDER BY data_length DESC ;

Comment to improve this query (Gigabyte consideration, ecc) are very welcomed.