MySQL Useful queries

mysql_useful_queries

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(CAST(ROUND(table_rows / 1000000, 2) AS CHAR), 'M')                                    rows,
       CONCAT(CAST(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2) AS CHAR), 'G')                    DATA,
       CONCAT(CAST(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2) AS CHAR), 'G')                   idx,
       CONCAT(CAST(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2) AS CHAR), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

SELECT `sql_text`, `total_query_time`, `number`, ROUND(`total_query_time`/`number`, 2) as `average` FROM (
SELECT `sql_text`, SUM(`query_time`) as `total_query_time`, COUNT(*) as `number` FROM `mysql`.`slow_log` WHERE `sql_text` LIKE "SELECT %" GROUP BY `sql_text` ORDER BY `total_query_time` DESC, `number` DESC
) sub;


SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;