Largest Tables in MySQL

Largest Tables in MySQL:

Following query will give largest tables of entire MySQL.

SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length);

Output

+--------------------+------------------------------------------------------+------------+ | Database | Table | Size in MB | +--------------------+------------------------------------------------------+------------+ | sys | host_summary_by_statement_type | NULL | | sys | x$ps_digest_avg_latency_distribution | NULL | | sys | x$user_summary | NULL | | sakila | sales_by_store | NULL | | sys | schema_tables_with_full_table_scans | NULL | | sys | x$statements_with_errors_or_warnings | NULL | | sys | memory_global_total | NULL | | sys | statements_with_runtimes_in_95th_percentile | NULL | | sys | x$latest_file_io | NULL | | sys | memory_by_host_by_current_bytes | NULL | | sys | waits_by_user_by_latency | NULL | | sys | x$waits_global_by_latency | NULL | | sys | x$statements_with_temp_tables | NULL | | sakila | sales_by_film_category | NULL | | mysql | help_topic | 1.58 | | sakila | payment | 2.13 | | sakila | rental | 2.66 | +--------------------+------------------------------------------------------+------------+ 306 rows in set (0.14 sec)

Following query will give largest table of entire MySQL.

SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 1;

Output

+----------+--------+------------+ | Database | Table | Size in MB | +----------+--------+------------+ | sakila | rental | 2.66 | +----------+--------+------------+

Following query will give top 10 largest tables of entire MySQL.

SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 10;

Output

+----------+--------------+------------+ | Database | Table | Size in MB | +----------+--------------+------------+ | sakila | rental | 2.66 | | sakila | payment | 2.13 | | mysql | help_topic | 1.58 | | sakila | inventory | 0.36 | | mysql | proc | 0.30 | | sakila | film_actor | 0.27 | | sakila | film | 0.27 | | sakila | film_text | 0.19 | | mysql | help_keyword | 0.16 | | sakila | customer | 0.13 | +----------+--------------+------------+

Following query will give top 5 largest tables of entire MySQL.

SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 10;

Output

+----------+--------------+------------+ | Database | Table | Size in MB | +----------+--------------+------------+ | sakila | rental | 2.66 | | sakila | payment | 2.13 | | mysql | help_topic | 1.58 | | sakila | inventory | 0.36 | | mysql | proc | 0.30 | +----------+--------------+------------+

Following query will give largest tables of particular Database in MySQL:

SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = "sakila" ORDER BY (data_length + index_length) DESC LIMIT 10;

Output

+----------+---------------+------------+ | Database | Table | Size in MB | +----------+---------------+------------+ | sakila | rental | 2.66 | | sakila | payment | 2.13 | | sakila | inventory | 0.36 | | sakila | film_actor | 0.27 | | sakila | film | 0.27 | | sakila | film_text | 0.19 | | sakila | customer | 0.13 | | sakila | address | 0.11 | | sakila | staff | 0.09 | | sakila | film_category | 0.08 | +----------+---------------+------------+

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: