Consultas MySQL útiles
Las 10 bases de datos más grandes del servidor
MySQL:
-
SELECT
-
count(*) TABLES,
-
table_schema,CONCAT(ROUND(sum(table_rows)/1000000,2),'M') rows,
-
CONCAT(ROUND(sum(data_length)/(1024*1024*1024),2),'G') DATA,
-
CONCAT(ROUND(sum(index_length)/(1024*1024*1024),2),'G') idx,
-
CONCAT(ROUND(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
-
ROUND(sum(index_length)/sum(data_length),2) idxfrac
-
FROM information_schema.TABLES
-
GROUP BY table_schema
-
ORDER BY sum(data_length+index_length) DESC LIMIT 10;
Número total de tablas, registros, tamaño de datos y tamaño de índices en nuestro servidor
MySQL:
-
SELECT count(*) TABLES,
-
CONCAT(ROUND(sum(table_rows)/1000000,2),'M') rows,
-
CONCAT(ROUND(sum(data_length)/(1024*1024*1024),2),'G') DATA,
-
CONCAT(ROUND(sum(index_length)/(1024*1024*1024),2),'G') idx,
-
CONCAT(ROUND(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
-
ROUND(sum(index_length)/sum(data_length),2) idxfrac
-
FROM information_schema.TABLES;
Los 10 motores de almacenamiento más utilizados
MySQL:
-
SELECT ENGINE , count( * )
-
TABLES , CONCAT( ROUND( sum( table_rows ) /1000000, 2 ) , 'M' )
-
ROWS , CONCAT( ROUND( sum( data_length ) / ( 1024 *1024 *1024 ) , 2 ) , 'G' )
-
DATA , CONCAT( ROUND( sum( index_length ) / ( 1024 *1024 *1024 ) , 2 ) , 'G' ) idx, CONCAT( ROUND( sum( data_length + index_length ) / ( 1024 *1024 *1024 ) , 2 ) , 'G' ) total_size, ROUND( sum( index_length ) / sum( data_length ) , 2 ) idxfrac
-
FROM information_schema.TABLES
-
GROUP BY ENGINE
-
ORDER BY sum( data_length + index_length ) DESC
-
LIMIT 10 ;
Términos relacionados: table schema, count tables, table rows, g data, select count, desc
